Finding last value in a row

rambollet

New Member
Joined
Mar 12, 2002
Messages
18
What formula will return the the first value to the right?

For example: A1=empty cell, B1=3, C1=empty cell, D1=0, E1=5, F1=empty cell.
What does the formula need to be in G1 to return the value of the first cell to the left than contains 0 or a value, or in this case 5 (E1)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Please excuse my typo. The question should be: "What forumla will return the first value to the LEFT?"
 
Upvote 0
On 2002-03-13 14:25, rambollet wrote:
What formula will return the the first value to the right?

For example: A1=empty cell, B1=3, C1=empty cell, D1=0, E1=5, F1=empty cell.
What does the formula need to be in G1 to return the value of the first cell to the left than contains 0 or a value, or in this case 5 (E1)

=INDEX(A1:F1,MATCH(9.99999999999999E+307,A1:F1))
 
Upvote 0
Thanks.

Plug in your formula and got the results I needed. Now I just need to spend some time figuring out the formula so I can understand it.
 
Upvote 0
Aladin, this is the second time you've posted a similar reply and not being a mathematician what is 9.9(al 14 of them)+307 represent?

SamS
 
Upvote 0
On 2002-03-13 20:03, SamS wrote:
Aladin, this is the second time you've posted a similar reply and not being a mathematician what is 9.9(al 14 of them)+307 represent?

SamS

Sam & Rambollet,

9.99999999999999E+307 is the biggest positive number that Excel knows.

When MATCH (with match type 1) fails to find for a numeric lookup value a matching numeric value in a row or column range to which it's applied, it returns the row (or column) number of the last cell in the range which houses a numeric value.

Given this behavior of MATCH, we feed it that huge number as lookup value for which, failing to find a mathing value, the function is bound to return the row/column number of the last cell of numeric type.

Thus, the formula exploits the fact that there will be practically no numeric value as big as 9.9...E+307 in the range tested.

Aladin
This message was edited by Aladin Akyurek on 2002-03-13 23:12
 
Upvote 0
On 2002-03-13 23:12, Aladin Akyurek wrote:
.... it returns the row (or column) number of the last cell in the range which houses a numeric value.
More precisely (I think): it doesn't return the row- or columnnumber, but the value of the last cell in the range wich houses a numeric value.
BTW Aladin: your knowledge is admirable!!
 
Upvote 0
On 2002-03-14 00:03, Albert 1 wrote:
On 2002-03-13 23:12, Aladin Akyurek wrote:
.... it returns the row (or column) number of the last cell in the range which houses a numeric value.
More precisely (I think): it doesn't return the row- or columnnumber, but the value of the last cell in the range wich houses a numeric value.
BTW Aladin: your knowledge is admirable!!

Thanks Albert. MATCH does really return the row/column number of the last numeric cell. When that's fed to INDEX, you get the value the last numeric cell houses. I think you had the latter in mind.

Aladin
 
Upvote 0
On 2002-03-14 01:05, Aladin Akyurek wrote:
Thanks Albert. MATCH does really return the row/column number of the last numeric cell. When that's fed to INDEX, you get the value the last numeric cell houses. I think you had the latter in mind.
You are right Aladin!
Thank you for the rectification.
 
Upvote 0
Rehashing an old thread here, but can this formula be altered to find the last text value in a range (similar to the cell references provided above)?
Thanks!
CG
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top