Find position of last non zero value in row

[horizonflame]

Hi All

I am looking for a formula to find the column number (i.e A=1, B=2) for the last value above 0 in cell range B2:B53.

I'd appreciate a non-array formula.

Thanks

 

[MARK858]

Not sure how you would achieve this with a "non-array formula"

and the column number for the range B2:B53 will always be 2.

 

[Rick Rothstein]

I am looking for a formula to find the column number (i.e A=1, B=2) for the last value above 0 in cell range B2:B53

Maybe it is just me, but I do not understand your question. If we are checking range B2:B53 for the last value above 0, isn't that column always going to be 2 (for Column B)?

 

[horizonflame]

Sorry it should be B2:BA2 as a row!

 

[MARK858]

Actually the below might work as a non-array formula if you want the column number of the last non-zero number in the range B2:BA2.

=LOOKUP(2,1/(B2:BA2<>0),COLUMN(B2:BA2))

 

[horizonflame]

Actually the below might work as a non-array formula if you want the column number of the last non-zero number in the range B2:BA2.

That's brilliant, thank you.