Formula to retrieve last value in range
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Formula to retrieve last value in range
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?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula to retrieve last value in range
Are these numeric values, text values or a mixture?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Formula to retrieve last value in range
Hans, numeric values only.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula to retrieve last value in range
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.
=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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Formula to retrieve last value in range
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.
=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.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula to retrieve last value in range
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)))
If you actually want the formula to return 6, change it to
=DAY(INDEX(E16:AC16,MATCH(9.99999999999999E+307,E16:AC16)))
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Formula to retrieve last value in range
Nathan
There's no place like home.....
There's no place like home.....