returning last (numeric) value in a row, coping with blanks in the row.

[slamanager]

Hi,

Just a quick one, whats the best formula (non array) to use to locate and return the last number on a row.

I have data starting in Column C up to Column K - for Rows 3 - 20
In col B i have this formula =IFERROR(INDEX(C3:K3,COUNT(C3:K3)),"")

Which works and returns the last number in the row but if there is a blank cell between C and K then it wont find the last number just the number after that blank.

Example

_,4,1,1,1,1,4
_,4,2,2,4
_,4,3,_,4,_5

how do i get the last example to show
_,5,3,_,4,_,5

the last number may not be a large number in every example.
?

 

[Aladin Akyurek]

Hi,

Just a quick one, whats the best formula (non array) to use to locate and return the last number on a row.

I have data starting in Column C up to Column K - for Rows 3 - 20
In col B i have this formula =IFERROR(INDEX(C3:K3,COUNT(C3:K3)),"")

Which works and returns the last number in the row but if there is a blank cell between C and K then it wont find the last number just the number after that blank.

Example

_,4,1,1,1,1,4
_,4,2,2,4
_,4,3,_,4,_5

how do i get the last example to show
_,5,3,_,4,_,5

the last number may not be a large number in every example.
?

Try...

=IFERROR(LOOKUP(9.99999999999999E+307,C3:K3),"")

 

[slamanager]

All ok and tested many thanks

 

[Aladin Akyurek]

All ok and tested many thanks

You are welcome. Thanks for providing feedback.

Note. The big number that is used as look up valuecan be found in Excel's help under "limits".