Please excuse my typo. The question should be: "What forumla will return the first value to the LEFT?"
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)
Please excuse my typo. The question should be: "What forumla will return the first value to the LEFT?"
=INDEX(A1:F1,MATCH(9.99999999999999E+307,A1:F1))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)
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.
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,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
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 ]
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.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.
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.On 2002-03-14 00:03, Albert 1 wrote:
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.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.
BTW Aladin: your knowledge is admirable!!
Aladin
You are right Aladin!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.
Thank you for the rectification.
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
Like this thread? Share it with others