Return the value in the last cell of the list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to look at 4 columns of data, and return to a the value of the last
cell in the 4 column range...

For Examples: What is the formula to go into Cell A1, A2, and A3?

A B C D E
1 return '3" 2 3

A B C D E
2 return "50" 2 3 50

A B C D E
3 return "400" 2 3 50 400
 
You could try this:
=OFFSET(A1,0,COUNTA(B1:Z1))
that's set up for row 1, just set the "Z" column far enough to the right to
include all possible used columns in the row.

Will return the WRONG answer if there are any empty cells to the left of the
last entry. Example: cell E1 has 29 in it and cells B1 and C1 have entries,
but D1 is empty, then it will pickup wrong value.
 
WOW FAST RESPONSE, AND IT WORKS!!! THANKS

JLatham said:
You could try this:
=OFFSET(A1,0,COUNTA(B1:Z1))
that's set up for row 1, just set the "Z" column far enough to the right to
include all possible used columns in the row.

Will return the WRONG answer if there are any empty cells to the left of the
last entry. Example: cell E1 has 29 in it and cells B1 and C1 have entries,
but D1 is empty, then it will pickup wrong value.
Click to expand...
 
That will work as long as the cells are filled from left to right and in a
contiguous block.

This formula is more robust:

=LOOKUP(10^10,B1:E1)
 
T. Valko's solution above may actually be a better one, since you don't have
to worry about empty cells within the list if you use it.
 
T. Valko said:
That will work as long as the cells are filled from left to right and in a
contiguous block.

This formula is more robust:

=LOOKUP(10^10,B1:E1)
Click to expand...

And it'd be even more efficient (recalc and storage) if you replaced the
unnecessary expression with a numeric constant.

=LOOKUP(1E10,B1:E1)
 
Harlan Grove said:
And it'd be even more efficient (recalc and storage) if you replaced the
unnecessary expression with a numeric constant.

=LOOKUP(1E10,B1:E1)
Click to expand...

Well, I hate using the pedantic 9.99999999999999E+307 but I do like your
suggestion. Let's see if I can remember to use it.
 
Back
Top