formula to return last value entered in a range
Posted by Dwight on July 10, 2001 11:47 AM
cells L5:L16 correspond to the 12 months of the year (L5= January, L6 February, etc.) and will contain whole numbers entered after the conclusion of each month. I need a formula for L4 which will return the last number entered in the range L5:L16. If there are no numbers entered in the range, I would like for L4 to return zero.
Thanks.
Posted by Mark W. on July 10, 2001 11:51 AM
=MAX(L5:L16)
Posted by Dwight on July 10, 2001 11:59 AM
Nope. Want the last number entered, not the largest.
Posted by Mark W. on July 10, 2001 12:09 PM
Re: Nope. Want the last number entered, not the largest.
Based on your description provide an example where
the last number entered wouldn't be the largest!
Posted by Aladin Akyurek on July 10, 2001 12:52 PM
Re: Nope. Want the last number entered, not the largest.
Mark -- Given my familiarity with Dwight's data, I believe he's looking for something like:
=INDEX(L5:L16,COUNT(L5:L16))
Caveat: If the range is filled by means of a formula, that formula can better avoid putting a zero in this range. If there isn't any value to enter, then the formula should produce a blank.
PS. Took a long vacation or too busy at work?
Posted by Mark W. on July 10, 2001 1:03 PM
Re: Nope. Want the last number entered, not the largest.
> Took a long vacation or too busy at work
Mandatory Q3 1-week vacation as a cost saving measure!
Both semiconductors and wireless communications sales are
not good right now.
> I believe he's looking for something like...
I suspect you're right. I'm trying to get him to
"think out of the box". Given his problem description
MAX() accomplishes same with fewer function calls.
: Based on your description provide an example where : the last number entered wouldn't be the largest!
Posted by Dwight on July 10, 2001 1:15 PM
all i get is "0"
Aladin,
Even as familiar as you are with the project from hell, you may have misunderstood. No matter what I fill in the range L5:L16, the formula
=INDEX(L5:L16,COUNT(L5:L16))
seems to return zero. If L5 (Jan) contains a "10", L6 (Feb) contains a "20", and L7 (March)contains a "15", I'm looking for a formula which returns "15".
I did take a few days off to celebrate anniversary of America's independence from imperialist European control. You were not spared an e-mail.
Thanks,
Dwight
Posted by Aladin Akyurek on July 10, 2001 1:28 PM
Re: all i get is "0"
Dwight,
If you can modify the formula that populates the L5:L16 range in such a way that it produces numbers including 0's up to the most recent month, the INDEX formula would be a far better choice than the array formula that you now have.
Aladin Even as familiar as you are with the project from hell, you may have misunderstood. No matter what I fill in the range L5:L16, the formula =INDEX(L5:L16,COUNT(L5:L16)) seems to return zero. If L5 (Jan) contains a "10", L6 (Feb) contains a "20", and L7 (March)contains a "15", I'm looking for a formula which returns "15". Thanks, Dwight
Posted by Mark W. on July 10, 2001 1:35 PM
Re: all i get is "0"
He's all yours.... : ) : Even as familiar as you are with the project from hell, you may have misunderstood. No matter what I fill in the range L5:L16, the formula : =INDEX(L5:L16,COUNT(L5:L16)) : seems to return zero. If L5 (Jan) contains a "10", L6 (Feb) contains a "20", and L7 (March)contains a "15", I'm looking for a formula which returns "15". : Thanks, : Dwight
Posted by Aladin Akyurek on July 10, 2001 1:40 PM
Re: Nope. Want the last number entered, not the largest.
Both semiconductors and wireless communications sales are not good right now.
Yeah, I noticed that too because of the state a few miserable shares of mine in that sector is in...
I'm right now busy grading exams of "Querying rel db's. I'll spend writing a syllabus "Data Modeling: Spreadsheet & Relational Models" for the next year. I might call you in! So no vacation this year for me, not in a literal sense anyway.
