Sum column apart from last entry

bradsalmon

New Member
Joined
Oct 7, 2014
Messages
2
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

bradsalmon

New Member
Joined
Oct 7, 2014
Messages
2
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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:
Rich (BB code):
9.99999999999999E+307
Name G1 as BigNum via the Name Box.

G2, just enter:
Rich (BB code):
=MATCH(BigNum,D:D)
Then invoke:
Rich (BB code):
=(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))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,315
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top