MPG & More

[Private]

I created a spreadsheet to calculate my MPG & other figures whilst on a journey in the motorhome.
I have (hopefully) attached a screenshot below as a sample & included the Excel file for anybody who may find it useful to use.
It is locked to prevent the accidental deletion of the formulae, but can be unlocked with the password "WildCamping" to enable full modification to your own requirements.
If you spot any errors in the calculations please let me know & I will correct & re-post.

View attachment JourneyInNumbers.zip

 

[malagaoth]

I created a spreadsheet to calculate my MPG & other figures whilst on a journey in the motorhome.

I am assuming that you dont like sleeping at night!

I feel unwell every time I pour dead dinosaurs into my M/h! having it all laid out in front of me in black and white would ensure that I never sleep again

 

[RichardHelen262]

Each to their own, I struggle keeping up to the accounts that I have to, so I definitely couldn't do them for my holidays, I doubt I could tell you to within the nearest £100 what I spend when on my holidays.

 

[Wooie1958]

I created a spreadsheet to calculate my MPG & other figures whilst on a journey in the motorhome.
I have (hopefully) attached a screenshot below as a sample & included the Excel file for anybody who may find it useful to use.
It is locked to prevent the accidental deletion of the formulae, but can be unlocked with the password "WildCamping" to enable full modification to your own requirements.
If you spot any errors in the calculations please let me know & I will correct & re-post.

View attachment 60105View attachment 60104

Nice 1 :wave:

I`m sure several members will find that very helpful, i keep a check on consumption but not as detailed as that :dance:

I am assuming that you dont like sleeping at night!

I feel unwell every time I pour dead dinosaurs into my M/h! having it all laid out in front of me in black and white would ensure that I never sleep again

There was no need for that, did someone force you to come into this thread and read it ?

 

[phillybarbour]

Very helpful and thanks for posting. I use a free app called MPG Calculator which does most of that other than the exchange rate element.

 

[2cv]

Thanks for putting that up. I did a similar thing several years ago playing with a PSION mini computer. I entered the data for a car I had. What was interesting was to see how each year the fuel consumption got worse in the winter and better in the summer. Apparently this is due the extra choke required when starting in cold weather.
As has been said, could be useful to compare slightly different tyre pressures or other variables such as supermarket v garage fuels or normal v "super" diesel.

 

[Private]

Development

Thank you for the comments.
I did this from scratch as a learning & refresher process in using Excel more than to calculate my MPG; there will be plenty of others who have done that before me & better.
By not searching for what had gone before I hope to have added something different though, & maybe the currency calculator part is that difference.

To produce a more accurate MPG figure I believe the sat-nav mileage should be used as opposed to the vehicles odometer & I began experimenting with that on the second sheet.
Unfortunately I have a tendency to require variety in my leisure & I'm already on to my next challenge so it may be a while before I return to it.

 

[malagaoth]

There was no need for that, did someone force you to come into this thread and read it ?

WOAH!!!!!
It was I thought a fairly light hearted response - it was certainly intended that way.


If the poster wishes to keep a precise record of every penny spent on fuel on his holidays (or the number of dead flies on his front bumper) fine, good luck to him.

I use my M/h for enjoyment not so that I can obsess over every penny I spend when using it - and my post was intended to reflect that

 

[maingate]

I keep track of fuel, price and mileage in a Diary. Usually in January I work out my totals and averages etc. the old fashioned way and complete my Annual costs and mileage ...... then I never bother with them until the following January. It's just a way of amusing myself on a long, cold Winters night. Amusing is probably the wrong word to use when I total the cost of fuel.

 

[jagmanx]

Spreadsheet or Diary ?

I keep track of fuel, price and mileage in a Diary. Usually in January I work out my totals and averages etc. the old fashioned way and complete my Annual costs and mileage ...... then I never bother with them until the following January. It's just a way of amusing myself on a long, cold Winters night. Amusing is probably the wrong word to use when I total the cost of fuel.

Whilst I admit to being a "near Nerd" and like to keep my Spreadsheets up to date
Spreadsheet 1 a bit like a diary (so a daily record) but with columns for Camp location Odometer reading (hence daily distance) Camp costs Diesel gas Food Ferry/Tolls Tourist clothes Miscellaneous other MH costs
A 2nd spreadsheet just for gas and diesel hence Gas litres per day and Diesel MPG
All similar to the OP

I do find a paper diary handy to record whilst travelling and sometimes to write down GPS details
Camp and others GPS are all recorded on my Tablet using Maps.me bookmarks

Keeping the Spreadsheets up to date takes about 5 mins daily or every other day and is interspersed with email facebook and next day planning and downloading Dashcam "footage"

WE spend what we need or want to we both just like to know !

 

[harrow]

I used to work out the fuel consumption and write it on the back of the paper receipt in mpg and then put it in the ashtray,

once I got 5 or 6 I averaged them out.

What I found years ago with a petrol car the fuel consumption dropped as the spark plugs fouled up, as the car was burning oil.

I found easier to do on paper as a dyslexic,

but my wife says I am just too lazy to read.

:wave:

 

[Private]

WOAH!!!!!
It was I thought a fairly light hearted response - it was certainly intended that way.


If the poster wishes to keep a precise record of every penny spent on fuel on his holidays (or the number of dead flies on his front bumper) fine, good luck to him.

I use my M/h for enjoyment not so that I can obsess over every penny I spend when using it - and my post was intended to reflect that

Whilst your first comment may have been light hearted this comment clearly is not; it is derogatory towards me even though I ‘liked’ your first comment & have also explained my main driver for creating the spreadsheet was not simply to know my MPG.

As others have mentioned there are many useful purposes for keeping a track on fuel consumption, none of which stop them (or I) from enjoying their motorhome trips.
My guess would be that those who know their consumption have the least need for it for fiscal purposes.

Working out how to calculate a complex formula in Excel may well keep me awake at night, but being able to afford the costs of a holiday (however long) do not; it is my ability in the former that has created the latter.

Thank you for your support G.

 

[barryd]

Mrs D keeps Excel records of just about everything. She can tell you how much gas to the litre we have used over a year, money spent on trips out, fuel and mileage for the bike and the van, food, money spent on aires, campsites etc. The one thing that was useful was the gas usage over time as I can now guess depending what time of year it is how much is left in the Gaslow tank as you build up a profile over time.

The rest I couldnt give a flying fig about, it is what it is. I reckon our van does 22mpg no matter how you drive it. I think if you pushed it off a cliff it would still be doing 22mpg. Our trips are long in duration so it doesnt really matter. Spread over four months the cost of diesel although a big chunk is not that great for such a long time away. Our miles are reduced considerably though because we do more miles on the bike on a long trip than the van and that does in excess of 100mpg.

 

[r4dent]

this reply is for spreadsheet and maths nerds only

If you spot any errors in the calculations please let me know & I will correct & re-post.

1) In the spreadsheet there is an underling assumption that the vehicle is filled to exactly the same level every time.
If I'm paying motorway prices I only put enough in to get me to somewhere that is a little cheaper.
When I monitored my mpg by spreadsheet I had a column for "extra litres to fill up to top". This was only an estimate but gave far more accurate results.

2) In columns Q & R you have calculated the average journey MPL / MPG by averaging the individual fill up MPL/MPGs.
This is only valid if each of the fill ups is for a trip of equal length.

You should be calculating a weighted average by totaling milage and fuel and calculating consumption from these.
e.g.
Trip 1 100 miles; 9 litre (2 gal); mpg = 50
Trip 2 300 miles ; 45 lite (10 gal) mpg = 30
average mpg (using your method)= 40

True average mpg (400 mile /; 12 gal) = 33.3

3) Tidy up the formulae

a)The SUM function is superfluous in rows H-R.
b) You could take out a lot the ISBLANKs by changing the option for displaying zero values
c) Where you have only 1 price add them together (the 2nd one is zero)

e.g the formula in J3 could be "=D3 * (G3+E3)". a lot simpler than your version.

On a small spreadsheet this doesn't really matter, but on large sheets the performance improvement can be impressive.

Please don't be offended, in a previous existence I lectured on spreadsheet design and audited other peoples work.
One thing I always told them was that letting someone else check their work was never a waste of time and that listening and heeding always improved their own skills.
(I still get requests from ex-colleagues which are paid for in beer when we meet up. )

I recently had a dispute with Santander which went to the Financial Ombudsman.
Santander made an offer supported by a spreadsheet and the Ombudsman recommended tht I accept it.
I pointed out three errors in the spreadsheet and Santander revised their offer to 3 times the original one.





To the non mathematicians ... Yes I do have a life !

 

[2cv]

1) In the spreadsheet there is an underling assumption that the vehicle is filled to exactly the same level every time.
If I'm paying motorway prices I only put enough in to get me to somewhere that is a little cheaper.
When I monitored my mpg by spreadsheet I had a column for "extra litres to fill up to top". This was only an estimate but gave far more accurate results.

2) In columns Q & R you have calculated the average journey MPL / MPG by averaging the individual fill up MPL/MPGs.
This is only valid if each of the fill ups is for a trip of equal length.

You should be calculating a weighted average by totaling milage and fuel and calculating consumption from these.
e.g.
Trip 1 100 miles; 9 litre (2 gal); mpg = 50
Trip 2 300 miles ; 45 lite (10 gal) mpg = 30
average mpg (using your method)= 40

True average mpg (400 mile /; 12 gal) = 33.3

3) Tidy up the formulae

a)The SUM function is superfluous in rows H-R.
b) You could take out a lot the ISBLANKs by changing the option for displaying zero values
c) Where you have only 1 price add them together (the 2nd one is zero)

e.g the formula in J3 could be "=D3 * (G3+E3)". a lot simpler than your version.

On a small spreadsheet this doesn't really matter, but on large sheets the performance improvement can be impressive.

Please don't be offended, in a previous existence I lectured on spreadsheet design and audited other peoples work.
One thing I always told them was that letting someone else check their work was never a waste of time and that listening and heeding always improved their own skills.
(I still get requests from ex-colleagues which are paid for in beer when we meet up. )

I recently had a dispute with Santander which went to the Financial Ombudsman.
Santander made an offer supported by a spreadsheet and the Ombudsman recommended tht I accept it.
I pointed out three errors in the spreadsheet and Santander revised their offer to 3 times the original one.





To the non mathematicians ... Yes I do have a life !

I never cease to be amazed by the variety of skills available in this forum.

 

[Private]

Thank you

1) In the spreadsheet there is an underling assumption that the vehicle is filled to exactly the same level every time.
If I'm paying motorway prices I only put enough in to get me to somewhere that is a little cheaper.
When I monitored my mpg by spreadsheet I had a column for "extra litres to fill up to top". This was only an estimate but gave far more accurate results.

2) In columns Q & R you have calculated the average journey MPL / MPG by averaging the individual fill up MPL/MPGs.
This is only valid if each of the fill ups is for a trip of equal length.

You should be calculating a weighted average by totaling milage and fuel and calculating consumption from these.
e.g.
Trip 1 100 miles; 9 litre (2 gal); mpg = 50
Trip 2 300 miles ; 45 lite (10 gal) mpg = 30
average mpg (using your method)= 40

True average mpg (400 mile /; 12 gal) = 33.3

3) Tidy up the formulae

a)The SUM function is superfluous in rows H-R.
b) You could take out a lot the ISBLANKs by changing the option for displaying zero values
c) Where you have only 1 price add them together (the 2nd one is zero)

e.g the formula in J3 could be "=D3 * (G3+E3)". a lot simpler than your version.

On a small spreadsheet this doesn't really matter, but on large sheets the performance improvement can be impressive.

Please don't be offended, in a previous existence I lectured on spreadsheet design and audited other peoples work.
One thing I always told them was that letting someone else check their work was never a waste of time and that listening and heeding always improved their own skills.
!

Thank you for your analysis & the extra education.
I have no training in Excel & only know how to use it through searching the internet to find solutions to the needs I have. It is useful to have an expert look over my amateur efforts & comment.
Far from being offended, I am pleased to receive the constructive advice.

With reference to your particular comments:
1) Personal preference I would suggest on this one as I choose to ignore any KM/L or MPG figures based on the tank not being filled up as even estimating the extra required would only lead to an estimated figure for MPG. Maybe I should set a flag column to blank the figures if the tank is not filled.
2) {Good spot. KM/L should actually be M/L as I did not convert to KM, only to litres. This is the type of genuine error I need to correct. Column O is actually showing miles per litre, not kilometers per litre as per its heading.}
Correction Edit. Column O is correctly calculating KM/L. I guess reference to MPL was typo by r4dent.

With regard the actual average anything less than a complete tank fill can only be an estimate as you know.
Although my sheet calculates the trip average, if the tank is not filled on successive fueling stop it is a superfluous calculation. My hope was that the 'journey MPG', when followed by a note showing the tank was filled, would be accurate.
My sample appears to bear that out as the figures are very close on each calculation when accompanied with a 'Full' note.
I will attempt to modify my calculation, using your calculation method, to make it more relevant during the "Not Full" fillings.

3)
a) I did not know that. I thought I always had to use SUM function for calculations. I will investigate further to learn when it is & is not required.
b) This is one I need to learn more about. I used ISBLANK as I don't like the zero's appearing before a row had been used. I will seek out the alternative you suggested. I could not fathom how to stop the £, € & - symbols appearing in columns J & K even after using ISBLANK.
C) That is much tidier but it does not work with the exchange rate calculation. I will see if I can use the example offered to include the exchange rate too.

Again, thank you for taking the time to analyse my efforts & the constructive advice offered.

 

[Asterix]

Wow,after reading this thread I'm starting to wonder if I should start tracking my mpg...or shoot myself!

 

[Wully]

I’ve got a great system when I put my hand in my pocket and it’s empty that’s when it’s time to go home works a treat. But I take my hat off to youse guys who can keep records and budget like that. I’m scared to try it then I couldent lie to myself that my vans great on fuel or I only spend 20 quid in the pub last night ide probably pack it in if I realy knew what I was spending

 

[barryd]

I bet none of you keep track of the true cost of keeping a motorhome on the road though. All those repairs, servicing, extras, insurance, tax, the list goes on. I think Ive had a couple of biggies. A rear axle was about £2600, damp issue about £1400. I had injectors and some other servicing done earlier this year and that topped over £1000. Getting a right hand drive exhaust in France one year cost me £400. I dread to think what the last decade would add up to.

Having said all that we have had pretty much nearly 5 years away in the van over the past ten and some adventures and memories that most people outside of the motorhome world could only dream of.

 

[jagmanx]

Oh yes

I bet none of you keep track of the true cost of keeping a motorhome on the road though. All those repairs, servicing, extras, insurance, tax, the list goes on. I think Ive had a couple of biggies. A rear axle was about £2600, damp issue about £1400. I had injectors and some other servicing done earlier this year and that topped over £1000. Getting a right hand drive exhaust in France one year cost me £400. I dread to think what the last decade would add up to.

Having said all that we have had pretty much nearly 5 years away in the van over the past ten and some adventures and memories that most people outside of the motorhome world could only dream of.

As ABBA would sing
I do I do I do.......

Well not everyting (eg toilet paper and Formil)

Like you I had a damp issue similar price (£1600)
new LB
4 tyres
Bodywork repairs ( My wheel spats fell off ...honest guv !)
Solar Panel etc £400
Gas-it £600
In January 2018 new cambelt
Winter storage £300
Servicing and MOT
Tax Insurance