MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Return last value entered in a range


Posted by Dwight on April 12, 2001 9:19 AM

b2:n2 are months in which I enter dollar amounts. Want a formula for cell q2 to return the value for the most recent month entered. Revised a formula which Aladin had kindly provided for what seemed to be almost identical situation (range was a column instead of a row), but it always returns the first month (b2) instead of the last, whether I reference the range as b2:n2 or n2:b2. Can someone help. The formula I tried is:

{=INDIRECT(ADDRESS(MAX((N2:B2<>0)*(ISNUMBER(N2:B2))*(ROW(N2:B2))),COLUMN(N2:B2)))}

Thanks,Dwight


Posted by Aladin Akyurek on April 12, 2001 9:57 AM

Dwight

Am I understanding correctly that you want retrieve the last value entered in a row that grows (that is, gets new values added)?

Is this what you looking for?

=INDIRECT(ADDRESS(ROW($B2$1:$N$2),MAX(($B$2:$N$2<>0)*(COLUMN($B$2:$N$2)))))

This is an array-formula that should be entered by hitting CONTROL+SHIFT+ENTER at the same time.

Aladin

Posted by Dave Hawley on April 12, 2001 10:28 AM

Hi Dwight

Noy sure if I have got you, but this formula will return the last entry in B2:N2. It will go past blank columns as well.

=INDEX(B2:N2,1,MATCH(1E+25,B2:N2))

If you expect a number larger than 1e+25 then just increase it.


Dave


OzGrid Business Applications

Posted by Aladin Akyurek on April 12, 2001 10:42 AM

Dwight:

You may also consider along the most recent suggestion:

=INDEX(B2:N2,COUNTA(B2:N2))

This will return a 0 if that is the last value entered.

Aladin

Posted by Aladin Akyurek on April 12, 2001 11:04 AM

By the way...

if you have blank cells in the middle of the range, a small modification is required to my latest suggestion:

=INDEX(B2:N2,COUNTA(B2:N2)+COUNTBLANK(B2:N2))

In the end, I'd advice using shortest formula.

Aladin

Posted by Aladin Akyurek on April 12, 2001 12:04 PM


Dwight:

If you don't have text values which you would like to return, just use the formula Dave suggested (Forget about the formulas containing COUNTA: They are flawed). You can even adapt it to a column range.
If you are interested in returning text values, the array formulas are the ones that can be used.

Cheers.

Aladin

Posted by Dwight on April 12, 2001 12:18 PM

Works great! Thanks, Aladin

Posted by Dwight on April 12, 2001 1:14 PM

Aladin, One more time please

You were right to keep exploring: I do need (if possible) a formula which will return a zero if it is the last entry (your first formula did not) and which will ignore blank entries. Your last suggestion seems to regard all blank cells as zeros, as it returns zero unless I enter a number in N2. Any further refinements possible?

Posted by Aladin Akyurek on April 12, 2001 1:56 PM

Re: Aladin, One more time please

You need to adapt the array-formula in order to return a 0 value if it is last value as follows:

=INDIRECT(ADDRESS(ROW(B2:N2),MAX((ISNUMBER(B2:N2))*(COLUMN(B2:N2)))))

Aladin