Right-most value 1

[robertsquestion]

Hi,

I'm using Excel 2003 and have the following question. I'm looking for a formula that gives me the right-most value in a range of cells. I tried the following formula:
=INDIRECT(ADDRESS(ROW(A1);MATCH(0;A1:E1;-1)))
This formula gives me the rightmost value in the range A1:E1. That works fine if the range only contains numbers. But as soon as you enter letters in the range of cells, the formula doesn't see this as a value.

Does anyone know how to change this formula (or another formula) so that it just gives me the value of the rightmost filled cell, regardless if there's a number or a letter in the cell?

Thanks in advance for your help!

Regards,
Robert
The Netherlands

 

[AnotherHiggins]

[tab]MATCH(0,A1:E1,-1)
looks for a number, not text.
[tab]MATCH("",A1:E1,-1)
looks for text, not a number.

If you combine these you come up with:
[COLOR=blue white]=INDIRECT(ADDRESS(ROW(A1),MAX(MATCH(0,A1:E1,-1),MATCH("",A1:E1,-1))))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

I just realized that the above suggestion will fail unless both numbers AND text appear in the range.

Let's go about this another way....
[tab][tab](Note: for further info on any of the following functions, see Excel's help file)

By using an array formula, we can find the last non blank cell. An array formula will evaluate each of the cells in a range (A1:E1 in our case). This is important: Array formulae are entered by using [Ctrl]+[Shift]+[Enter] instead of just enter. When done correctly, curly brackets - {} - will appear around your formula.

[COLOR=blue white]=MAX(IF(NOT(ISBLANK(A1:E1)),COLUMN(A1:E1)))[/color]
entered as an array will return the last non-blank column in your range.

To get the data displayed in that cell, we can use the Offset function.

[COLOR=blue white]=OFFSET(A1,0,MAX(IF(NOT(ISBLANK(A1:E1)),COLUMN(A1:E1),0))-COLUMN(A1))[/color]
Remember, this is an array formula so it must be entered with [Ctrl]+[Shift]+[Enter].

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[robertsquestion]

Great, thanks for your help John!!

Robert