building a formula

Try:

=LOOKUP(9.9999999E+307,1:1)

where 1:1 represents the row the values are in.

Originally Posted by dianahatesboys

that would work if the final cell's value equals zero but the final cell's value can equal anything (and can even be repeated throughout a row. for example

A B C D E F
1 () () 2 0 1
() () () 2 () ()
() () 2 () () 0

any ideas? first row should return 1, second row should return 2, third row should return 0.

THANKS!

which formula are you talking about...

Mine placed in a cell below those rows and copied down will extract the 1, 2, and 0 as required.

Originally Posted by NBVC

which formula are you talking about...

Mine placed in a cell below those rows and copied down will extract the 1, 2, and 0 as required.

oh i am sorry i did not see your reply before i responded!

thanks, yes this formula is exactly what i am looking for. i don't know if this is a stupid question but can you explain why/how it works? i'd like to understand why 9.9999999E+307 is relevant in particular.

and how do i build a formula to return the column number that contains the rightmost value? in my previous example, it would return 6, 4, 6.

THANK YOU AGAIN!

ETA: never mind - i was able to figure out what i really needed. i wanted it to return a value from another cell based on the column the rightmost cell was in but i figured it out. THANKS AGAIN!

Apologies, I assumed that you wanted the last non-zero value, that's what my suggested formula above gives.

According to "Excel specifications and limits" the largest value you can have in a cell is 9.99999999999999E+307 (NBVC used a shorter version). When you use LOOKUP with a lookup value, i.e. that large number, which is greater than any number in the lookup range, the last number is returned, which is what you need

If you want the position of the last number you could just change LOOKUP in NBVC's suggestion to MATCH, i.e......

=MATCH(9.9999999E+307,1:1)

....although if you want to return, say, the value in row 1 corresponding to the last numeric value in row 2 you don't need MATCH, just amend the LOOKUP formula like this:

=LOOKUP(9.9999999E+307,2:2,$1:$1)