How do I find the last non-blank non-zero positive value in a column/range?

Question

I have a column, let's say column A, which contains a list of numbers, which can be positive or negative. Those numbers are then followed by a varying number of blanks (i.e. =IF(some_condition_is_met,"",value). I want to find the last non-blank non-zero positive value in that column.

E.g.

    Column A
    0.00
    0.00        
    0.00
    0.00
    15.00
    -5.00
    0.00
    blank
    blank
    blank

I want the formula to ignore the blanks, and the zeros, and the negative values, and return 15.00 in this case.

I would prefer not to use an array formula, if possible.

The other solutions I've seen are less sophisticated, e.g. they only find the last non-zero or non-blank value.

I can't figure this one out!

Answer 1

I would be interested in knowing your reasons for stating that you would "prefer not to use an array formula, if possible.".

If, by "array formula", you mean a formula which requires the keystroke combination of CTRL+SHIFT+ENTER in order to activate it, then yes, we are not obliged to employ such here.

If, however, you were alluding to the more general (and arguably more correct) definition of an "array formula" as being a formula which must process all elements within the array passed to it on an individual basis (within this definition, we would define functions such as AGGREGATE, SUMPRODUCT and others as "array formulas", despite their not requiring CSE), then no, due to the nature of your problem we will not be able to avoid such a set-up.

As such, it is extremely important to keep the range passed to a minimum. I have used A1:A10 here, which obviously you can change as required, though you should be careful not to make it too arbitrarily large (and certainly don't reference entire columns!), since, for each additional cell referenced, extra calculation will be required.

=LOOKUP(1,0/(0+A1:A10>0),A1:A10)

Regards