Averaging Fuel Data

[Sahara53]

First time here, I'm currently recording fuel usage data for my work vehicle (school bus). I record as normal mileage, fuel taken and then using my spreadsheet calculate the fuel usage. I then transfer the monthly data across to another sheet which only shows those monthly stats which will provide an annual Km/L and L/100km (sorry I"m in Aussie land!).

My question now is how do I calculate the annual figures to arrive at an accurate figure? My current sheet shows a greater usage rate which I am sure is incorrect. Wondering if I might find some assistance for this problem.

Many thanks

[jhren]

Km/L and L/100km are rates. Summing rates are meaningless. For example, say you track your km/hr, which is a rate of speed, every ten seconds for a city trip (i.e. start, stop, various rates of speed in between)...

0kmph
10
15
10
10
20
5
0

The sum is 70... and you didn't go 70kmph at any point.

However, you can use the sum in figuring the average. The formula you have used in your example, divides the sum (Total) by 3... when you have 4 entries. You'd have to divide by 4 for the average.

Rather than getting in how to do that particular formula, its a whole lot easier to use the following one in C16... =AVERAGE(C4:C13) ...and copy right to D16.

[Sahara53]

Thanks jehren, I knew that there was an easy answer. Having been away form this sort type of work now for a couple of years I'm pretty rusty. Your answer makes it so much easier.
Thanks for your rapid response.
Cheers for now.

[FDibbins]

Hi and welcome to the forum

You need to go back to your source data to calc anual averages. It is mathematically incorrect to average averages. The best way to do this, is to sum the Km's/mth onto your annual summary sheet, sum your litres/mth onto your summary sheet, and then base the anual averahge onthe total of those to values

If you upload the full workbook (no senstive info), I will take a look and see if I can help you set something up.

[Sahara53]

HI FDibbins, I did think that is what I was working on but hey - me no expert by any means. I'll drop in the full sheet with this reply and you can see what it is that I'm trying to acheive.
Thanks for your time and response.
Mmm - I don't see how I can upload the file from this reply??

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Sahara53]

Ok now attached is the full file as requested.

[FDibbins]

Take a look at the attached and let me know if that is something you can work with?

[Sahara53]

Yep that is terrific it now certainly provides the detail. I'm not very good at converting such data into a chart, I assume that this data could converted into some form of ongoing chart?

[FDibbins]

1 thing to note with what I gave you, you MUST keep the tab/sheet name and the month name identical - you will note I removed the " 13" from the tab names, the indirect() formula I used requires that they both be the same - I used indirect so make copying down easier

Regarding the chat, excel's charts are pretty much fool-proof to create, just follow the instructions and you should be good to go

[Sahara53]

Ok noted, thanks for that. I am trying to maintain this record as a means of keeping check of my driving to ensure that I am doing so at the most economical possible. I figure that one day the question maybe asked and I will be able to provide a rapid answer ie cost per student.
Thanks again for your assistance, I will have a crack at the chart sometime later, for now tis drawing to end of the weekend and now start prep work for the new week.
Cheers

[FDibbins]

Happy to help and thanks for the feedback