Require a function to return a last non zero value in a column

Hi,

There's absolutely nothing wrong with this solution:

=LOOKUP(9.99E+307,1/($C$9:$C$208<>0)/($C$9:$C$208<>""),$C$9:$C$208)

However, it always strikes me as slightly illogical, and perhaps also habitual? Again, there's nothing wrong with this, but perhaps for the sake of interested readers of this post wondering about these constructions with a so-called "big number", I thought I would just take the opportunity to point out that, in this particular example, its use is not at all necessary, and perhaps also slightly counter-intuitive.

Having said that, I can understand why it is taught that this value should be used, particularly in cases where the 'last number in a range is being sought', etc., etc., and I'm not trying to say that it shouldn't be, just that, well, let me explain...

Since the array returns for ($C$9:$C$208<>0) and ($C$9:$C$208<>"") will consist of TRUE/FALSE only, when we calculate:

($C$9:$C$208<>0)/($C$9:$C$208<>"")

we will get something along the lines of, for example:

{TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;...}/{TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;...}

which will evaluate to:

{1;0;#DIV/0!;1;1;1;#DIV/0!;1;...}

Hence, since when we next take the reciprocal of this array of values (from 1/($C$9:$C$208<>0)/($C$9:$C$208<>"")):

{1;#DIV/0!;#DIV/0!;1;1;1;#DIV/0!;1;1;#DIV/0!;1;#DIV/0!;...}

we will only ever get either a 1 or a #DIV/0!. In fact, this will always be the case, as you can easily verify.

The point is then, that unlike if we were passing an array of arbitrarily large numbers to the LOOKUP function, in which case this 'big number' is justified (and necessary), here, since our largest value can only ever be 1, it is sufficient to write:

=LOOKUP(2,1/($C$9:$C$208<>0)/($C$9:$C$208<>""),$C$9:$C$208)

Regards