Find Last Numeric Cell Using Formulas

parry · Dec 22, 2003

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

2

12

22

13

34

3

4

TypevalueofcellF1

2

(Text)

5

LastCellusingCountA

$F$1

6

Sheet1

Yogi Anand · Dec 22, 2003

Hi Parry Try ...

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

Aladin Akyurek · Dec 22, 2003

parry said:
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 · Dec 22, 2003

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 · Dec 22, 2003

parry said:
...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 · Dec 22, 2003

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

Yogi Anand · Dec 22, 2003

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 · Dec 22, 2003

Thanks again guys.

Aladin Akyurek · Dec 22, 2003

parry said:
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 · Dec 22, 2003

Yogi Anand said:
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.

Find Last Numeric Cell Using Formulas

parry

MrExcel MVP

Excel Facts

Yogi Anand

MrExcel MVP

Aladin Akyurek

MrExcel MVP

parry

MrExcel MVP

Aladin Akyurek

MrExcel MVP

parry

MrExcel MVP

Yogi Anand

MrExcel MVP

parry

MrExcel MVP

Aladin Akyurek

MrExcel MVP

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Find Last Numeric Cell Using Formulas

MrExcel MVP

Excel Facts

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock