EXCEL Again

[Felix]

Made a basic MPG calculator for my piss poor van, as i am noting what changed i made, parts bought until it improved. When i drag the formula down the cells i get this text. How to a get rid of it as the bottom of last row i have an average formula what will not show with them being there.

mpg.jpg (190.94 KiB) Viewed 748 times

[Trinity765]

=IFERROR(B1/A1,"")

Replace B1/A1 with your original formula. Anything you put in the speech marks will appear as text, in the instance above it will be a blank cell.


You could, for example, put "No Value" and it will return No Value instead of #div/0

[Felix]

=IFERROR(B1/A1,"")

Replace B1/A1 with your original formula. Anything you put in the speech marks will appear as text, in the instance above it will be a blank cell.


You could, for example, put "No Value" and it will return No Value instead of #div/0

Most excellent. Worked a treat, thank you very much

[Zimbo]

Have you thought of using Fuelly instead? Open it on your phone, when you fill up enter the odometer reading, litres put in and cost / litre and it'll do all the calculations for you - MPG, cost/mile, averaged MPG over a timeframe etc. I've been using it for years, it's excellent, and free!
https://www.fuelly.com

[Felix]

Have you thought of using Fuelly instead? Open it on your phone, when you fill up enter the odometer reading, litres put in and cost / litre and it'll do all the calculations for you - MPG, cost/mile, averaged MPG over a timeframe etc. I've been using it for years, it's excellent, and free!
https://www.fuelly.com

This is really just a temp thing. My van has issues as you can see its giving around 25MPG. I just want to keep tabs on how the mpg is while i start a process of illuminations by changing parts. Crazy thing was i got 34mpg the first 100 miles after my remap but now its back down so 25. About to go and change the air and fuel filter before spending money on injectors.

[Rockburner]

Without being personal.... Are you somewhat lead-footed?

I only ask because in the van i had for a while the mileage was significantly improved simply by driving gently. And with the distances i was doing it made zero difference to journey times.

[Felix]

Without being personal.... Are you somewhat lead-footed?

I only ask because in the van i had for a while the mileage was significantly improved simply by driving gently. And with the distances i was doing it made zero difference to journey times.

No. Driving miss daisy for the past 500 miles or so (When i noticed the shit MPH) Folk on the Maxus groups all say between 35 to 38mpg. The past 300 miles the van has not been over 70mph what is just over 2500rpm. Gear change is between 2000/2500 as since the remap i dont have to rev it higher to get up to speed. Taking hills in a higher gear. Lots of local bends where i would have to be in third to manage to take away when i got round it. Can now take them in 4th. Running and injector cleaner just now and changed filters this morning. Must admit its feeling smoother and i have just did an 80 mile round trip to the borders.

[Mr. Dazzle]

Regarding the OP

How are you doing the maths? So for example in Row 1 you've got 5.5 Gallons and 17.73 litres, but those numbers don't "fit". In UK gallons 17.73 litres is ~3.9 gallons.

You've also got 97.5 miles and 5.5 gallons adding up to be 24.97mpg, whereas (assuming those two numbers are the ones you want) it's actually 17.27mpg.

I can't see the rest of the sheet though, so I don't know how the maths are working. I think you might actually have the correct mpg calc (97.5 miles and 17.73 litres would actually be ~25mpg as stated). Dunno, just curious how the maths work really

=IFERROR(B1/A1,"")

Replace B1/A1 with your original formula. Anything you put in the speech marks will appear as text, in the instance above it will be a blank cell.


You could, for example, put "No Value" and it will return No Value instead of #div/0

Most excellent. Worked a treat, thank you very much

Tip for you...

Excel treats "N/A" as differently to "NA, No Value, Error"...anything really. It can "see" the term N/A and ignore it. So it's generally better to get it to put in an N/A than something else, that way you can see the cell is doing something.

So in this example all I've done is change that top cell between "N/A" and "Div/0" and you can see it'll still happily do the average at the bottom in one case, but not the other.

image_2024-01-28_153040071.png (4.4 KiB) Viewed 676 times

[Felix]

Miles divided by litres gives gallons multiply by 4.54 gives mpg no?????

[Mr. Dazzle]

Miles divided by litres gives gallons multiply by 4.54 gives mpg no?????

*cogs whirring*

Err...yeah I think so It's just a slightly odd order of calculation! Works out the same in the end though, hence why your last column is the number I'd expect.

The column you've labelled "gallon" is actually miles per litre, then you've multiplied by liters/gallon (i.e. there are 4.54 litres in a gallon) to get mpg.

All comes out in the wash though, tis the same maths in the end and only a nerd would worry about it. Hence my interest

[Felix]

The column you've labelled "gallon" is actually miles per litre, then you've multiplied by liters/gallon (i.e. there are 4.54 litres in a gallon) to get mpg.

So it is this is the formula =SUM(D5/E5) This is what i have for the MPG =SUM(F5*4.54)

[Mr. Dazzle]

Sums aren't needed. That extra calculation must be slowing your Excel down by at least 0.00000001s.

[KungFooBob]

Sums aren't needed. That extra calculation must be slowing your Excel down by at least 0.00000001s.

He's probably got manual calculation enabled to stop it dragging his machine down whenever he changes a value.

[Mr. Dazzle]

The whole thing could be a Python script in the new versions of Excel anyway

I don't even speak Python, I get one of the young folk to do it. Ironically they don't get my Python references. No one ever expects them.

[roadster]

Common way of avoiding empty cells is to test for a blank ( or other value ) and make the result blank in that case
e.g.
=IF(A1="","",(A1+B1) )
A1+B1 is the result if A1 in NOT blank otherwise the result is blank. When you propagate this by dragging down the entire column the cell references increment as you would expect. The first argument [ A1="" ]could be any logical test or expression.

[Mr. Dazzle]

There's a specific command in Excel for checking blankness, or lack thereof. The highly originally named ISBLANK(). Returns TRUE and FALSE.

So you could do =IF(ISBLANK(A1),"put some numbers in here you numpty",A1+B1).

[Felix]

Now they say you need to run this over a couple of tank fill to see a difference but after 100miles i see a difference in a smoother engine and its cleaning as so far i am getting up to 3mpg more https://www.hydra-int.com/diesel-power-blast.html BTW it stinks