Fuel Consumption Problem

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

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)

"Rusty" <kenrussell@optus YOUR HAT home.com.au> wrote in message
news:[email protected]...
> 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 wrote:
> 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.

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)

<[email protected]> wrote in message
news:[email protected]...
> Bob Phillips wrote:
> > 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 wrote:
> 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.)

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>.


<[email protected]> wrote in message
news:[email protected]...
> Bob Phillips wrote:
> > 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.)
>

On Sun, 25 Jun 2006 10:32:30 +0100, "Bob Phillips" wrote in
microsoft.public.excel:

>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]...
>> 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.
[snip]

--
Michael Bednarek http://mbednarek.com/ "POST NO BILLS"

Hello,

Rusty, have a play with this sheet and see if it fits your needs.




"Rusty" <kenrussell@optus YOUR HAT home.com.au> wrote in message
news:[email protected]...
> 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
>
>

Michael Bednarek wrote:
> "Bob Phillips" wrote:
> >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.

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).

Errata....

I wrote:
> 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 ....

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



"Rusty" <kenrussell@optus YOUR HAT home.com.au> wrote in message
news:[email protected]...
>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
>

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



"Rusty" <kenrussell@optus YOUR HAT home.com.au> wrote in message
news:[email protected]...
>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
>

Rusty wrote:
> 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

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



"Bill Sharpe" <[email protected]> wrote in message
news:%[email protected]...
> Rusty wrote:
>> 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 wrote:
> 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)

Errata....

I wrote:
Rusty 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.

That works beautifully!

Thank you so much for your input.

Cheers,
Rusty

<[email protected]> wrote in message
news:[email protected]...
> Rusty wrote:
>> 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)
>