Find Last Numeric Cell Using Formulas

[parry]

How can you obtain the address of the last numeric cell in a range by using formulas? In the following example the last numeric cell in row 1 is E1. Cell F1 contains a formula which returns "" and is treated as text as per the type formula shown. Ctrl-RightArrow from cell A1 returns F1 as the last used cell in the row.

I can do this with a custom function or sub but how about a formula?
I have tried =ADDRESS(1,COUNTA(1:1)) but this returns cell F1. Any ideas?

Tammy.xls
	A	B	C	D	E	F	G
1	12	22	13		34
2	12	22	13		34
3
4		TypevalueofcellF1		2	(Text)
5		LastCellusingCountA		$F$1
6
Sheet1

 

[Yogi Anand]

Hi Parry:

One caution here --

In my formulation, the MATCH function looks in the specified range for 9.99999999999999E+307 (largest possible number that EXCEL can handle) -- and if it finds it -- returns that entry

So, if the number 9.99999999999999E+307 happens to be within the specified range, we would get an incorrect result

Please refer to the following illustration ...

y031222h1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	12	22	_	_	13	34a	b	c	k	9.99999999999999E+307	56		68
2	addressoflastnumericentryinrow1$J$1
3	12	22	_	_	13	34a	b	c	k	9.99999999999999E+307-1	56		68
4	addressoflastnumericentryinrow3$M$1
Sheet9

In row 1, I have housed 9.99999999999999E+307 in cell J1, so my formulation erroneously tells me that my last numeric entry is in cell J1, even though my last numeric entry is in cell M1

However, since I know EXCEL is trying to home in on 9.99999999999999E+307, in row 3, if I change the entry in cell J3 to 9.99999999999999E+307-1, now I can make EXCEL correctly report to me that the last numeric entry in row 3 is in column M

Practically speaking, this is not of concern because we will not be dealing with the largest possible number that EXCEL can handle anyway -- but I thought I will share this concern with you.

 

[Yogi Anand]

Hi Parry:

Just to clear up a little confusion in my Mon 22 Dec 2003 21 55 post, I am reposting the illustration ...

y031222h1.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	12	22	_	_	13	34a	b	c	k	9.99999999999999E+307	56		68
2	addressoflastnumericentryinrow1$J$1
3	12	22	_	_	13	34a	b	c	k	9.99999999999999E+307-1	56		68
4	addressoflastnumericentryinrow3$M$3
Sheet9

The last numeric entry found in row 3 is now coorectly displayed as $M$3 rather than $M$1.

 

[DBudd]

How do you get the number to show so you can use it in a Formula

 

[NateO]

Welcome to the board, DBudd.

Use Index() instead of Address(). Search the board for 'BigNum'.

 

[Aladin Akyurek]

How do you get the number to show so you can use it in a Formula

=LOOKUP(9.99999999999999E+307,Range)

where Range can be something like A2:A100, B:B, or B2:F2, all one-dimensional. Note that the formula fetches the last numeric value from Range.

Is this what you are asking for?

 

[DBudd]

Yes that work thanks. But also is there a way to have it find the last number in a row then move up one cell and display that number?

 

[Aladin Akyurek]

Yes that work thanks. But also is there a way to have it find the last number in a row then move up one cell and display that number?

You mean the numeric next-to-last value? And, what is the range of interest?

 

[DBudd]

I am working with coordinates and I have a formula that needs to use next to the last value of a column.

 

[Aladin Akyurek]

I am working with coordinates and I have a formula that needs to use next to the last value of a column.

Supposing that the range of interest is column A and the data starts at row 2...

The last numeric value:

=LOOKUP(9.99999999999999E+307,A2:A65536)

The numeric next-to-last numeric value:

=LOOKUP(9.99999999999999E+307,A2:INDEX(A2:A65536,MATCH(9.99999999999999E+307,A2:A65536)-1))

 

[DBudd]

That worked great. Thanks for your time.