Find Max Data within a range and repeat the max value next column between Zero

[davidlimtw77]

Hi Anyone know what is the excel formula to find the max value within a range and repeat the max value new column within cell with number greater than zero.

Thanks

David

 

[hagia_sofia]

Do you mean:

=IF(B2:B100>0,MAX(B2:B100),0)

 

[davidlimtw77]

That didnt solve the problem, I am looking for a solution to find the max value within the range and repeat max value next column between the zeos.

 

[Fluff]

How about

=IF(B2=0,0,IF(N(C1)=0,MAX(B2:XLOOKUP(0,$B2:$B$1000,$B2:$B$1000,$B$1000)),C1))

 

[davidlimtw77]

How about

=IF(B2=0,0,IF(N(C1)=0,MAX(B2:XLOOKUP(0,$B2:$B$1000,$B2:$B$1000,$B$1000)),C1))

That worked. Thanks for the tips.

 

[Fluff]

Glad we could help & thanks for the feedback.