Solved: IF statement

[maninjapan]

I have a column of price data for a stock which due to some errors in the data has some rows which return zero. This affects my calculations so in the next column I am using the following If statement to just refer to the price above
[VBA]=IF(B50<=1,B49,B50)[/VBA]

This works fine for the most part, however occasionally there are 2 or more zeros in a row. So I need to fix the if statement to keep going up the list till it finds a row with a price that isnt zero in it.

Is there a simple way to adjust this IF statement to do this?
Thanks.

[Bob Phillips]

This array formula should do it

=IF(B50<1,INDEX(B:B,MAX(IF($B$1:B49<>0,ROW($B$1:B49)))),B50)

[Bob Phillips]

This array formula should do it

=IF(B50<1,INDEX(B:B,MAX(IF($B$1:B49<>0,ROW($B$1:B49)))),B50)

[maninjapan]

Xld, thanks. I just tried it. The cells with correct numbers work ok, but the cells that have 0 in them just return #VALUE! Am I missing something here?

[Bob Phillips]

Did you array enter it?

[maninjapan]

Ok, didnt do that, ti works now. I have a few hundred rows though. I need this to be a multi-cell array formula right? (just going off the excel help here)

Thanks

[maninjapan]

Actually its ok, just realized after I add the first one, I can just copy and drag it down the whole column.

Thanks a lot for your help