last number in column
Posted by Jim S. on August 15, 2001 10:24 AM
I am looking for a way for Excel to find the last number
in a column and then add (1) to it. This column has both
text and numbers in it.
Any help would be greatly appreciated.
Jim
Posted by Mark W. on August 15, 2001 10:48 AM
If cells A1:A8 contains...
{1;6;3;5.2;4;"dog";2.6;"cat"}
...then the array formula,
{OFFSET(A1,MAX(ISNUMBER(A1:A8)*ROW(A1:A8))-1,)+1}
...will return 3.6
Note: Array formulas are entered using the
Control+Shift+Enter key combination. The
braces, {}, are not entered by you -- they're
supplied by Excel in recognition of a properly
entered array formula.
Posted by Aladin Akyurek on August 15, 2001 11:09 AM
Also...
=INDEX(A:A,MATCH(1E+30,A:A))
using Mark's example, you'll get 2.6.
Note. This non-array formula expects that there will never be a number as big as 1E+30 in column A.
Aladin
Posted by Mark W. on August 15, 2001 11:28 AM
This won't work for...
9.99999999999999E+307
...which is the largest value that Excel can handle.
Posted by Aladin Akyurek on August 15, 2001 3:25 PM
What is the prob that ... (Re: This won't work for...)
9.99999999999999E+307 would occur in a column of numbers, even in a worksheet Bill Gates might be using? Subatomic physics might perhaps produce huge numbers, even then...
An almost fail-safe procedure would be:
=INDEX(A:A,MATCH(MAX(A:A),A:A))
Aladin
===============
Posted by Mark W. on August 15, 2001 3:54 PM
No, I would have suggested...
Posted by Aladin Akyurek on August 15, 2001 4:23 PM
Re: No, I would have suggested...
Not exactly. see below.
Should have been: =INDEX(A:A,MATCH(MAX(A:A)+1,A:A))
This can't handle the extreme case of 9.99999999999999E+307.
Mea culpa:
=INDEX(A1:A4,MATCH(MAX(A1:A4)+DELTA,A1:A4))
will produce 5 (where DELTA is say 1).
Apparently, Gates's programmers took the liberty wrt MATCH (I believe also wrt LOOKUP) to default to the row number of the last cell in use instead of "no match" (#N/A). I share your concern, also because of the inconsistent behavior of MATCH when the range of interest consists solely of text values and/or blanks. That I tend to exploit this loophole is pure pragmatic.
I think they should introduce a function LASTCELL that returns the pos of last cell (row or column). Badly needed in order to compute a given range dynamicallly.
Posted by Mark W. on August 16, 2001 7:32 AM
But why the avoidance...
...of 9.99999999999999E+307? It seems that you'd
rather use MAX(A:A)+1 which entails both a function
call and an arithemtic operation when a constant
would suffice. Also, since 9.99999999999999E+307
is in the domain of numeric values there's still
a possibility (albeit, remote) of failure with
this formulation. It seems to me that the most
elegant approach would be to define a name,
"maxNum", as =9.99999999999999E+307, and use the
formula...
=INDEX(A:A,MATCH(maxNum,A:A))
...so if Excel's specfication was ever changed
then "maxNum" could be revised globally.
This topic begs the question, "Why doesn't
Microsoft have named constants for all of it's
limits?" Wouldn't that provide for better
configuration management of one's worksheets?
Posted by Aladin Akyurek on August 16, 2001 2:38 PM
Re: But why the avoidance...
When I propose to someone with a column of numeric data type, say,
=MATCH(1.0E+30,A:A)
I feel usually "guilty" about not explaining it, captivated by a "pragmatic silence"!
About avoiding the use of maxNum: I think I avoid probing (more) questions that require lengthy and/or technical explanations from the users who might not have the relevant knowledge to digest them (This is something I should better not have said).
> It seems to me that the most
Yep.
Absolutely. We should perhaps issue another proposal to MS.
Aladin
