PDA

View Full Version : Solved: IF statement



maninjapan
01-08-2010, 01:06 AM
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
=IF(B50<=1,B49,B50)

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
01-08-2010, 03:19 AM
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
01-08-2010, 03:19 AM
This array formula should do it

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

maninjapan
01-08-2010, 03:51 AM
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
01-08-2010, 04:02 AM
Did you array enter it?

maninjapan
01-08-2010, 05:11 AM
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
01-08-2010, 05:26 AM
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