Formula

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?
 
S

Shane Devenshire

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top