last cell in a range

1inus

New Member
Joined
Jul 6, 2006
Messages
40
I'm looking for a formula I can put in a cell that will reference the last cell in a single column range (A1:A10). After searching around I came across the following formula:

=INDEX(A1:A10,MATCH(9.99999999999999E+307,A1:10))

Is there a different formula I should use or is this one a good one? Also, I'm not understanding why the 9.99999999999999E+307.

thank you
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
In your formula,
=INDEX(A1:A10,MATCH(9.99999999999999E+307,A1:10))

The MATCH() function returns the relative row a value in the range A1:A10 matches, or is equal to, the number 9.9999999999999E+307. However, if it finds no match, it returns the number of rows it looked through. Note that you could use any reasonable number that will always be larger than any number that will be found in the range A1:A10. So, if the range contains the series 1, 2, 3...10, you could use 11 as your "large number". Probably, a large number, such as 1000, might be perfectly suitable, but, the excelent programmer who suggested the 9.99999999 etc.E+307 chose the largest number that Excel recognizes, just to cover all bases!

The INDEX() function returns the data that is in the cell on the row returned by the MATCH() function, counting from the first row in the range.
In your case, it should return the data in the last cell in column A.

If you need to know the last row number, rather than the data in that last cell, you could use, assuming cell A3 is the first cell in your range:
=row(A3)-1+MATCH(9.99999999999999E+307,A1:10)

Let us know if you are now satisfied with the above.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

I'm looking for a formula I can put in a cell that will reference the last cell in a single column range (A1:A10). After searching around I came across the following formula:

=INDEX(A1:A10,MATCH(9.99999999999999E+307,A1:10))

Is there a different formula I should use or is this one a good one? Also, I'm not understanding why the 9.99999999999999E+307.

thank you

See: http://tinyurl.com/83b2x
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
If still interested in showing the value in the last cell of your range A1:A10, try this:

=INDIRECT("a"&COUNTA(A:A))

And, this formula is dynamic, so it will show the last cell in the column, even as you add new data. How about it?
 

ckng

New Member
Joined
Oct 28, 2006
Messages
7

ADVERTISEMENT

hi
if my data starts from A3 instead of A1.. how do i edit the formula????

=INDIRECT("a"&COUNTA(A:A))
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
hi
if my data starts from A3 instead of A1.. how do i edit the formula????

=INDIRECT("A"&COUNTA(A:A))
You don't. But, you do have to have something in A1 and A2, either text or numbers. If either cell is empty, enter a single quote, which will not be visible, but will allow the formula to work.
 

Forum statistics

Threads
1,136,706
Messages
5,677,309
Members
419,685
Latest member
hennLow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top