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
