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 Try ...

=ADDRESS(1,MATCH(9.99999999999999E+307,1:1))

 

[Aladin Akyurek]

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?

...

=CELL("Address",INDEX(1:1,MATCH(9.99999999999999E+307,1:1)))

 

[parry]

Very clever Yogi! Thank you very much. Ive seen other people like Aladin use big numbers like that as well but I clean forgot about it. I must take more notes

Im curious, what forces Excel to go to the last numeric when the number is so large?

 

[Aladin Akyurek]

...Im curious, what forces Excel to go to the last numeric when the number is so large?

Binary search, invoked by match-type set to 1 or omitted.

 

[parry]

Thank you Aladin. Can you direct me to a site that explains what a binary search is or briefly explain this?

 

[Yogi Anand]

Hi Parry:

In trying to locate this largest possible number in the specified range EXCEL looks at all cells in the range with numeric entry, and when it reaches the last numeric entry in the range and (if this is equal to or smaller than the number to be matched -- and it is) so it returns that entry.

 

[parry]

Thanks again guys.

 

[Aladin Akyurek]

Thank you Aladin. Can you direct me to a site that explains what a binary search is or briefly explain this?

Here is a good piece on BS...

http://www.tbray.org/ongoing/When/200x/2003/03/22/Binary

 

[Aladin Akyurek]

Hi Parry:

In trying to locate this largest possible number in the specified range EXCEL looks at all cells in the range with numeric entry, and when it reaches the last numeric entry in the range and (if this is equal to or smaller than the number to be matched -- and it is) so it returns that entry.

It doesn't look at all cells when a binary search routine is invoked.