Sum column apart from last entry

[bradsalmon]

Hi all,

Seems like a simple thing but I haven't a clue where to start with this one.

I'm looking to create a simple fuel tracker with columns date, miles, litres, price. So far simple. After populating with a few entries I realised that the miles column represents data that the vehicle has already completed whilst the litres and price entries for the last entry represent fuel which has yet to be used and hence distorts figures such as mpg or pence per mile when calculations are made.

So my question is, is it possible to sum a column of numbers but to not include the last entry? At present I manually change the calculations each time I make a new entry but it would obviously be better if this was automatic.

Thanks in advance for any suggestions,
Brad

 

[Aladin Akyurek]

Coult you post the formula you have?

 

[bradsalmon]

Coult you post the formula you have?

For example a formula such as =(max(B:B)-min(B:B))/sum(D:D) to work out price per mile doesn't work correctly because it includes the most recent amount paid for fuel which is yet to be used.

THanks Aladin

 

[Aladin Akyurek]

For example a formula such as =(max(B:B)-min(B:B))/sum(D:D) to work out price per mile doesn't work correctly because it includes the most recent amount paid for fuel which is yet to be used.

THanks Aladin

G1, just enter:

9.99999999999999E+307

Name G1 as BigNum via the Name Box.

G2, just enter:

=MATCH(BigNum,D:D)

Then invoke:

=(MAX(INDEX(B:B,1):INDEX(B:B,$G$2-1))-MIN(INDEX(B:B,1):INDEX(B:B,$G$2-1)))/
  SUM(D:D,-LOOKUP(BigNum,D:D))