Excel formula to show the last number in a series of cells?

[Spyder757]

I need a formula to show the last number keyed in a series of cells.

Example:

Day 1: $15
Day 2: $25
Day 3: $30
Day 4:
Day 5:

Week Ending: $30 (this number will change when Day 4, and then finally Day 5's numbers are keyed)

Any ideas?

 

[chrisaroundtown]

How long is your list going to be?

If it is going to be quite short then reference each cell individually using an IF formula.

This could be:

=IF(B5="",IF(B4="",IF(B3="",IF(B2="",IF(B1="","",B1),B2),B3),B4),B5)

This is saying that if B5 is empty then check B4, then if B4 is empty check B3 and so on. As soon as it finds one that has a value it will return that value. Hence it will always return the value of the cell in the lowest position provided it isn't blank.

Thanks
Chris

 

[dcompto]

Maybe the following will help.

Excerpt from Microsoft Knowledge Base Article 213917...

You can use the following sample formula to find the value of the last valid cell in a column

=OFFSET(<StartCell>,MATCH(MAX(<Range>)+1,<Range>,1)-1,0)

where <StartCell> is the address of the first cell of a range, and <Range> is the address of the cells containing the data.

 

[GlennUK]

And slightly simpler than using MATCH is using COUNTA, as in this formula ...

=OFFSET($A$1,COUNTA($A$1:$A$10)-1,0)

replacing $A$1 with the start cell, and adjusting the range covered in the COUNTA function to suit your needs.


Cheers, Glenn.