Excel: Finding previous non-zero value in column [closed]

Question

I'm trying to find a formula that can find the most recent non-zero value in a column in Excel and call that value. For instance, the data may look like:

    A
1   0

2   0

3   5

4   0

5   0

6   1

7   0

In this data, if I were to apply the formula in the 7th row (on a third separate column), I would want it to call the 1 in the second column and 6th row. If the formula were applied in a separate column in the 5th row then I would expect it to call the 5 from the 3rd row.

Do let me know and thanks! I'd prefer if the answer was not in VBA!

Answer 1

You could try something like this (copied to all rows in column B):

In cell B1, put

= A1

Then in the cell below that, put

=IF(A2<>0, A2, B1)

and copy that down. What will happen is this: if the cell to your left is zero, you will look "above you" for the last "good" value. This "good" value will be one that was copied from a nonzero cell to the immediate left.

I tested it on a simple example and it seems to work. If you need more explanation let me know.