Formula to retrieve last value in range

[VegasNath]

I need a formula (AD16) that will return the last value in the range A16:AC16. This will be the last 'non-blank' cell in the range. Is there an easy way to do this?

[HansV]

Are these numeric values, text values or a mixture?

[VegasNath]

Hans, numeric values only.

[HansV]

Try this formula:

=INDEX(A16:AC16,MATCH(9.99999999999999E+307,A16:AC16))

9.99999999999999E+307 is the largest value one can enter in a worksheet, so it is extremely unlikely that it will actually be matched. Therefore the MATCH function will return the index of the last numeric value it finds in the range.

[VegasNath]

Thanks Hans. Is it possible to similar with dates?

=INDEX(E16:AC16,MATCH(9.99999999999999E+307,E16:AC16)) - This returns 40,304 (which I am assuming is the equivalent of 06th May 2010). I would like to return 6, as a day number that I can use to perform calculations on.

[HansV]

If you want to display 6, simply format the cell with the formula with the custom format d (which stands for the day number).
If you actually want the formula to return 6, change it to

=DAY(INDEX(E16:AC16,MATCH(9.99999999999999E+307,E16:AC16)))

[VegasNath]