function for last non-blank entry in column?

  • Thread starter Thread starter mp
  • Start date Start date
M

mp

Is there a function that would return the value in the last non-blank cell
in a colunm?
example
"a1" = 10,
"a2" = 25,
"a3" = 3,

formula in "a10" would produce 3

then if later "a4" = 1, "a10" would automatically = 1
(in other words it's not Max, nor Min, nor Sum, nor Average etc)

is that clear?
thanks
mark
 
Is there a function that would return the value in the last non-blank cell
in a colunm? example
"a1" = 10,
"a2" = 25,
"a3" = 3,
Click to expand...

=LOOKUP(1E100,A1:A3)

Although LOOKUP "requires" that the lookup table be in ascending
order, this use of LOOKUP works by coincidence because 1E100 is
presumably larger than any numeric value in the lookup table.

Note: If 1E100 is not larger enough, you could use 9E307, which is
closer to the maximum number that Excel permits to be entered. The
largerst computable number is (2^1023-2^970)*2.
 
Is there a function that would return the value in the last non-blank cell
in a colunm? example
"a1" = 10,
"a2" = 25,
"a3" = 3,
Click to expand...

=LOOKUP(1E100,A1:A3)

Although LOOKUP "requires" that the lookup table be in ascending
order, this use of LOOKUP works by coincidence because 1E100 is
presumably larger than any numeric value in the lookup table.

Note: If 1E100 is not larger enough, you could use 9E307, which is
closer to the maximum number that Excel permits to be entered. The
largerst computable number is (2^1023-2^970)*2.

very clever
thanks
mark
 
Back
Top