Formula

  • Thread starter Thread starter Ted Dawson
  • Start date Start date
T

Ted Dawson

I need to get the value of the cell in the last populated row in a column on
another sheet, and I use a formula like this to get it:

=LOOKUP(9.99999999999999E+307,Sheet4!E3:E30)

My problem is that I now need to calculate the value from the E column, so
until it is calculated, Excel shows the value to be $0.00 (the result of the
formula before any other values have been entered), so I'm picking up $0.00
as the last value.

How do I get the last value that has been calculated or greater that 0?
 
Hi,

You are going to need VBA, the Excel spreadsheet can't determine if a cell
is a formula or a value, at least not using a formula.

Also, your formula does not return the last populated cell it returns the
last cell with a numeric value.

More, you should simplify this formula to read
=LOOKUP(9^9,Sheet4!E3:E30)
unless your numbers are VERY large
9^9
which is 387,420,489
This portion of the formula only needs to be larger than the largest number
which could be found in the column. 10^10 would be 10,000,000,000!

For values greater than 0 the task is easy, use the following array formula:

=INDEX(A:A,MAX(ROW(A:A)*(A:A<>0)*ISNUMBER(A:A)),0)

to make it an array press Shift+Ctrl+Enter instead of Enter

If this helps, please click the Yes button,

Cheers,
Shane Devenshire
 
Back
Top