Return last number in a range

[PIJim]

I need to return the last numerical value in a range of cells.

Range is Y14:AB14
The formulas in these cells will either return a percentage value or #DIV/0!.
I want to return the numerical value furthest to the right.

thanks,
Jim

 

[Tuelor]

Whilst I am sure someone can do this only using Excel, its straight forward to create your own UDF

Function LastFigureInRange(byref rng as range) as string

Dim tempRng as range
Dim sTemp as string

sTemp = "n/a"

For each tempRng in rng

If IsNumeric(tempRng.Value) then
  sTemp = tempRng.Value
End if

Next tempRng

LastFigureInRange = sTemp

End function

Please forgive any typos as I did this on my phone!

 

[Haseeb Avarakkan]

Hello, Try

=LOOKUP(1E+100,Y14:AB14)

 

[PIJim]

lookup worked perfectly -thanks