Finding last value in a row

[rambollet]

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)

[rambollet]

Please excuse my typo. The question should be: "What forumla will return the first value to the LEFT?"

[Aladin Akyurek]

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))

[rambollet]

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.

[SamS]

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

[Aladin Akyurek]

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 ]

[Albert 1]

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!!

[Aladin Akyurek]

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

[Albert 1]

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.