gas mileage

[sal]

Hi I am trying to do an excel worksheet for gas mileage, where I am
having problems is the Miles per gallon. If I fill up the tank I am
ok, but if i don't fill up then I am screwed.
A B C d e
Fuel (litres) Odometer (km) Trip Distance km Per Liter filled
67.21 77913.1 377.2 5.61 y
68.085 78320.2 407.1 5.98 y
19.294 78795.7 475.5 0.00 y
26.346 78917 121.3 0.00 n
70.703 79068.1 151.1 2.14 y

I can get it to partially work by using the formula like this:
=IF(e3="Y",(c3)/a3,0)

So If tank is filled they it gives me a value and if it is not then I
get a 0.
My problem is the 2.14 c/a is not right any more.What I need to do now
is add 475.5+121.3+151.1/19.294+26.346+70.703 to give me 6.43.
Is there a formula to add the colums with the 0 values plus the new
column with the Y value in the c and a column and then divide them to
give me the right value. Or any other suggestions of doing this. I
used to use a program called vehicule record system and it did the
calculations right but have no idea how it was done.
If any one done such a spreadsheet and is willing to send it, it would
be appreciated, or a formula to do this.

Thanks in advance
Sal

 

[Kevin H. Stecyk]

sal wrote...

Hi I am trying to do an excel worksheet for gas mileage, where I am
having problems is the Miles per gallon. If I fill up the tank I am
ok, but if i don't fill up then I am screwed.
A B C d e
Fuel (litres) Odometer (km) Trip Distance km Per Liter filled
67.21 77913.1 377.2 5.61 y
68.085 78320.2 407.1 5.98 y
19.294 78795.7 475.5 0.00 y
26.346 78917 121.3 0.00 n
70.703 79068.1 151.1 2.14 y

I can get it to partially work by using the formula like this:
=IF(e3="Y",(c3)/a3,0)

So If tank is filled they it gives me a value and if it is not then I
get a 0.
My problem is the 2.14 c/a is not right any more.What I need to do now
is add 475.5+121.3+151.1/19.294+26.346+70.703 to give me 6.43.
Is there a formula to add the colums with the 0 values plus the new
column with the Y value in the c and a column and then divide them to
give me the right value. Or any other suggestions of doing this. I
used to use a program called vehicule record system and it did the
calculations right but have no idea how it was done.
If any one done such a spreadsheet and is willing to send it, it would
be appreciated, or a formula to do this.

Sal,

I have sent you a revised spreadsheet. All I did was to add two additional
helper columns. One for fuel and kms. These helper columns simply
accumulate fuel and distance when the tank is not filled.

Hope this helps.

Regards,
Kevin