CODEWITHSUNDEEP
excelexcel-formula
EndlessLoop
EndlessLoop

Reputation: 444

Does this variation on "return last numeric value in column" formula have any known fail conditions that anyone can see?

The parameters are:

  1. The column may contain either negative or positive numbers (including the decimals forms of proper fractions), blank cells, text or errors (like #DIV/0!), and will have data added to it over time.
  2. It uses LOOKUP in its vector form.

The formula (with Column A as the column in question) is:

=LOOKUP(1,-1/A:A^2,A:A)

The thinking is

  1. First make sure any negative numbers are positive by squaring them using A:A^2
  2. Make all the numbers negative by using -1/<resulting number from 1.>.
  3. Then, when LOOKUP goes looking for the value 1, it will exploit the behaviour of LOOKUP to return the last number in column for a value that it can't find that is greater than any of the numbers in the resulting array.

I think it works in all circumstances, but can anyone see something that breaks it?

Thanks in advance.

Upvotes: 0

Views: 77

Answers (1)

barry houdini
barry houdini

Reputation: 46331

It won't work if the last number is zero, that will generate a #DIV/0! error which LOOKUP will ignore

Why not

=LOOKUP(9.99999999999999E+307,A:A)

Upvotes: 3

Related Questions