Fuel Consumption Problem

[Rusty]

I need to build a worksheet that calculates MPG for a car whenever the tank
is filled. The tank is not filled each time, but some times only a few
gallons are put in.

I need a formula that will do the maths only when the tank is filled. It
would need to take into account the partial fillings in order to get the
correct MPG.

Any help would be greatly appreciated.

Cheers,
Rusty

 

[Bob Phillips]

Surely it is just sum of miles travelled divided by sum of gallons input?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[joeu2004]

Surely it is just sum of miles travelled divided by sum of gallons input?

Well, the sum of the gallons input to fill the tank. I think that it
is the point the OP is trying to make ("the tank is not filled each
time, but some times only a few gallons are put in").

But IMHO, the OP has not said enough about the spreadsheet design for
us to provide a constructive answer. In the simplest design, there
might be a row for each time gas is added to the tank, with columns
indicating the odometer reading (or miles since previous row), amount
of gas added and a flag to indicate that the tank was filled that time.
Then a suitable answer might sum the miles in rows between flags
(including the latest flagged row) and divide by the sum of the
correspondings amounts of gas. There are so many ways that could be
done, from the simple to the sublime. Perhaps the OP can offer more
details about the spreadsheet design, including a detailed example of
the desired result.

 

[Bob Phillips]

Exactly my point, my response was intended to stimulate more information
about the problem. We can second-guess all day long, but we will still be
second-guessing.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[joeu2004]

We can second-guess all day long, but we will still be second-guessing.

As you wrote elsewhere: "I agree, the spec was a little ambiguous, so
I made an assumption. Unfortunately, as I am sure that you know, that
is something that we (have
to) do very often in trying to give these solutions <g>".

I fully agree that mindreading is a required art in order to help
people with programming problems. I have been doing it for several
decades. It is little wonder that they cannot find solutions on their
own when they are unable to articulate the problem clearly. But in
this case, I think the OP was reasonably clear; just not sufficiently
complete.

Exactly my point, my response was intended to stimulate more information
about the problem.

To paraphrase the knight in the third "Indiana Jones" adventure: "He
chose unwisely" .

(Don't take that as harsh criticism. I am in a fanciful mood.)

 

[Bob Phillips]

No problems Joe, it's a slow day, time for philosophical debate <g>.

I just beg to differ, to my reading he wasn't clear enough for me to make
any assumptions. I either ignored him, or prodded him, I chose to prod <bg>.

As to the Indiana Jones quote, I am afraid that went straight over <ebg>.

BTW I am shocked to see you admitting to doing this for several decades. We
are supposed to be coming over as bright young things with sharp minds, not
a bunch of doddering old fogies (tbbgicm>.

 

[Michael Bednarek]

Surely it is just sum of miles travelled divided by sum of gallons input?

Exactly; and I can't see why the OP thinks it makes a difference whether
the tank has been fully filled or not.

"Rusty" wrote in message news:[email protected]...

[snip]

 

[joeu2004]

Exactly; and I can't see why the OP thinks it makes a difference whether
the tank has been fully filled or not.

If you travel 200 miles, then put exactly 2 gal into the tank but do
not fill the tank, you did __not__ get 100 miles to the gal (200 / 2).
In fact, you do not have enough information to compute the miles per
gal, unless you know how many gal were consumed. But the OP is
assuming only knowledge of how many gal were added and whether or not
the tank was filled, which I think are typical data.

On the other hand, if you travel 200 miles, add 2 gal but not fill the
tank, then travel 50 miles and put in 8 gal to fill the tank, your MPG
can be computed as 25 MPG (200+50) / (2 + 8).

 

[joeu2004]

Errata....

On the other hand, if you travel 200 miles

I should written: On the other hand, if you start with a full tank and
you travel 200 miles ....

 

[Rusty]

Sorry I triggered a philosophical discussion by not giving more information


Let me try again.

Example layout;

A B C D E
Date Miles F/N Gals MPG

31/12 150 N 5
07/01 200 F 9 25
10/01 250 N 10
12/01 125 N 5
15/01 50 F 4 22.4

Obviously the MPG cannot be calculated until the tank is filled, and then it
must take into account the partial fills.

F/N means Full/Not Full

Thanks for your assistance.

Cheers,
Rusty

 

[Bill Sharpe]

I need to build a worksheet that calculates MPG for a car whenever the tank
is filled. The tank is not filled each time, but some times only a few
gallons are put in.

I need a formula that will do the maths only when the tank is filled. It
would need to take into account the partial fillings in order to get the
correct MPG.

Any help would be greatly appreciated.

Cheers,
Rusty

The logic is very simple. Implementation can vary, as suggested by other
responders.

First, fill the tank and note the odometer reading(1).
When adding gas, you need only note the gallons added if tank isn't
filled completely.
When the tank is filled to capacity, note both the gallons added and the
odometer reading(2).
Subtract reading (1) from reading (2).
Divide by the total number of gallons added since the last fillup.
Continue...
As one who ALWAYS fills my tank up the procedure can be simplified. A
calculator works about as well as an Excel spreadsheet for this.

Bill

 

[Rusty]

Thanks Bill, I know the logic, but I need to know how to implement it in
Excel as a log book.

How do I tell Excel to do the calculation only when the tank is full? It
would be fine if there were the same number of partial fills in each
calculation, but that is not the case. I can do it manually by entering a
formula at each Fill, but I would like Excel to do that if it's possible.

Cheers,
Rusty

 

[joeu2004]

A B C D E
Date Miles F/N Gals MPG

31/12 150 N 5
07/01 200 F 9 25
10/01 250 N 10
12/01 125 N 5
15/01 50 F 4 22.4

There might be a more-elegant solution, but someone had offered the
following KISS solution to a similar problem of mine some time ago.

The key is to have some helper columns, which I will call F and G.
These can be hidden, if you like. Column F will be the accumulator of
miles between fill-ups, and column G will be the accumulator of
gallons. Create the following formulas, then copy them down their
respective columns. I will call the row with 31/12 row 2; but it can
be row 3, if you like. If you start in row 2, as I did, do not put
titles in F1 and G1.


E2: =IF(C2="f", F2/G2, "")
F2: =IF(C1="f", B2, F1+B2)
G2: =IF(C1="f", D2, G1+D2)

 

[joeu2004]

Errata....

I wrote:

A B C D E
Date Miles F/N Gals MPG
31/12 150 N 5
07/01 200 F 9 25
[....]
E2: =IF(C2="f", F2/G2, "")
F2: =IF(C1="f", B2, F1+B2)
G2: =IF(C1="f", D2, G1+D2)

Of course, I meant to write "F" instead of "f". I am sure you can
figure that out for yourself.

 

[Rusty]

That works beautifully!

Thank you so much for your input.

Cheers,
Rusty