# 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:

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?

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

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

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.

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.

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.

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

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