Fuel consumption app/spreadsheet template wanted

blue.peter

Thank you all for your input. I've now written a spreadsheet for the purpose, making use of some of your suggestions. It's not really what I was hoping for, but it'll do the job.

In case it helps anyone else who comes along in the future looking for something in which to record their fuel consumption, I'll provide an image of it and set out the formulae and formatting that it uses. Since this is written for use in the UK, with its weird mish-mash of units, distances are in miles, fuel purchased is in litres, but the consumption is measured in miles per imperial gallon. The figure of 4.54609 in a couple of the formulae below is the conversion factor between litres and gallons. It can be omitted if you want fuel consumption in miles per litre, or replaced by 3.78541 if you want US gallons.

This is written in LibreOffice Calc, but I'm sure it can be easily adapted to other spreadsheets, e.g., Microsoft Excel.

Formulae (in all cases, these should be copied to the rest of the column below for all rows in which there are entries):

E4 =A4-A3

F4 =(A4-$A$3)/(ROW()-3)

[The ROW() function in LibreOffice returns the current row number. The offset of -3 allows for the first entry being in row 3. This formula therefore takes the difference between the current date and the start date and divides by the number of entries. An alternative version that can be used if preferred would be =AVERAGE($E$3:E4). This one takes the average of all entries in column E.]

G3 =D3/C3

H4 =(B4-B3)/(C4/4.54609)

I4 =(B4-$B$3)/(SUM($C$4:C4)/4.54609)

Formatting by column:

A = Date (This is critical. If your spreadsheet doesn't recognise the data in this column as dates, the formulae in columns E and F won't work.)

B = Number, general

C = Number, 2 decimal places

D = Currency, 2 decimal places

E = Number, general

F = Number, 1 decimal place

G = Currency, 3 decimal places

H = Number, 2 decimal places

I = Number, 2 decimal places

Formatting for row 1 is 12pt bold. For all other rows, 10pt standard weight is used. The font that I've used throughout is my default of Liberation Sans.

TooManyPoints

If you want to save having to replicate your formulae every time you make an entry, you can modify all of them to include a conditional test. For example, in E4, instead of having simply =A4-A3, you could have

=IF(A4>0,(A4-A3),""  

This means that a test is performed on cell A4 (which might contain a date). If it does, the calculation A4-A3 is returned. If it does not then the cell will remain blank. You can then replicate all your formulae down as far as you like and they will only show the results of a calculation when a date has been entered in column A.

blue.peter

TooManyPoints said:

If you want to save having to replicate your formulae every time you make an entry, you can modify all of them to include a conditional test. For example, in E4, instead of having simply =A4-A3, you could have

=IF(A4>0,(A4-A3),"" 

Thanks very much.

I didn't know that this was possible - probably because I never thought to ask the question. I only discovered the ROW function a day or two ago, and that was because I asked Google how to obtain the row number for use in the formula that I was writing for column F of my new spreadsheet. (I make no pretence to be any sort of a spreadsheet expert. I'm retired now, but made my living with words rather than numbers. I use spreadsheets for purely domestic purposes, so the maths is generally pretty simple. Although I have an A-Level in the subject, the most complicated thing I've used since I left university is simultaneous linear equations - second-form stuff.)

As things stand at present, I've done as suggested above and copied my formulae down about ten rows or so below the last entry and therefore have a load of division by zero errors showing. This is hardly elegant, so I think I'll follow your suggestion. The syntax of your example looks really strange (an opening bracket without a closing one and a lot of punctuation at the end), so I'll get to grips with that first. But that's fine. I'm interested enough to want to do it.

MEM62

blue.peter said:

For some years, I've been tracking my fuel consumption on a phone app. However, that app is no longer supported and doesn't work properly with the latest version of Android.

Wanted: an app or spreadsheet template that'll work on my Windows 11 PC. (I use LibreOffice, in case it's relevant.)

I'll want to input date, mileage at refuelling, fuel bought in litres and cost, and have it calculate consumption in miles per imperial gallon, both since last refuelling and cumulative. There's no need for anything that tracks maintenance costs as well - I've got that covered elsewhere.

I expect that I could write a spreadsheet to do it, but I'm sure that (a) someone else has already done it and (b) theirs will be slicker than anything I could produce.

Any offers, please? I'm willing to pay for something suitable, but free is better.

Fuelly.com.  Job done!  

blue.peter

MEM62 said:

Fuelly.com.  Job done!  

This was suggested on Monday. I took a look at it, but decided that it wasn't for me.

TooManyPoints

The syntax of your example looks really strange (an opening bracket without a closing one and a lot of punctuation at the end),

Sorry, I missed the bracket off the end. It should be:

=IF(A4>0,(A4-A3),"")

The syntax interpretation is

IF([test],[value if test true],[value if test false])

The punctuation after the second comma is a pair of inverted commas. It provides a blank cell if the test result is false. If you left that bit of the syntax out like this:

=IF(A4>0,(A4-A3),)

and the result of the test was false, you would get zero returned (which I imagine you don't want). 

There's all sorts of things you can do with "IF". If you incorporate its associates "AND" and "OR" you can test two things at once For example:

=IF(AND(A1>1,B1<1000),1,0)

This tests cell A1 for being greater than 1 and B1 for being less than 1,000. If both those tests are true, 1 is returned. If one or both of them is false, 0 is returned. If you did the same with "IF,OR" and either was true,1 would be returned. Only if both were false would 0 be returned. Of course you can change what is returned depending on the tests to anything you like, a number or some text.


Give it a try!

blue.peter

TooManyPoints said:

Sorry, I missed the bracket off the end. It should be:

=IF(A4>0,(A4-A3),"")

The syntax interpretation is

IF([test],[value if test true],[value if test false])

The punctuation after the second comma is a pair of inverted commas. It provides a blank cell if the test result is false. If you left that bit of the syntax out like this:

=IF(A4>0,(A4-A3),)

and the result of the test was false, you would get zero returned (which I imagine you don't want).

Yes, thanks. I read the relevant help page, had a bit of a play around, and worked all that out (including the significance of ""). Those commas are, in effect, shorthand for the THEN and ELSE that I seem to remember using when I did a bit of programming in ALGOL in the 1970s.

I now have several rows of apparently blank cells at the bottom of my spreadsheet that magically get populated as soon as a date is entered at the beginning of the row. Much smarter that DIV/0 errors

It's totally irrelevant, but I think that the test A4>0 only works for dates from 1 January 1900 onwards. I think that A4<>0 would work for all dates except 31 December 1899. So I guess that it would need to be something like A4<>"" to get a TRUE result for anything except a blank cell. As I say, though, it's completely irrelevant in the context of calculating my cars' fuel consumption - I didn't buy my first one until 1980. And my fuel purchase notebooks only go back as far as my third car, bought in 1986. (I've no idea what happened to the earlier one.) Your suggestion works beautifully in the present context.