Average MPG

[Guest]

Hello everyone,
I am trying to get a worksheet I have been working on for a long time to
work with the Average miles per gallon. What I want to do is take the MPG for
the tank and avg it in with the previous MPG for the previous tanks of gas. I
want to know the Average miles per gallon on a steady basis. I tried using
the =AVERAGE(A2:A97), however it keeps changing each time I attempt to input
a new number. For instance the last time I filled up it brought my AMPG to
20.25. The next time I put a number in for MPG for the next tank I want to
know what the AMPG is with out the 20.25 changing. Does anyone understand
what I am trying to say? I am not sure if I put it across to clear. Any help
on this issue will be greatly welcome. THank you all in advance for your time
and effort.

Scott

 

[Paul B]

Scott, not sure, but if you have your MPG for each tank in a column all you
need to to is average that column to get the average for all of them, is
this what you want?

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 2003
** remove news from my email address to reply by email **

 

[Sandy Mann]

Scott,

I don't quite follow what you are asking. How is your data laid out?
Especially what data is in column A and what cell(s) is the average formula
in?

Regards

Sandy

 

[BenjieLop]

I have this setup for my MPG calculations:

Column A -- contains the total gallons
Column B -- contains total mileage
Column C (miles/gallon) -- =(Bx/Ax)

In my worksheet, I have up to 300 rows in each column hence, th
average MPG is =average(C1:C300). This formula works even if the 300t
row has not been reached yet.

I am not even sure if my setup is applicable in your situation. It i
probably best to show how your data are laid out and then, go fro
there

 

[Guest]

Thank you to all who replied... thus far this is what I have..

Column A: Date
Column B: Odometer
Column C: Trip
Column D: Gallons
Column E: Price
Column F: MPG
Column G: Avg. MPG

Now I already have Column F: MPG working correctly. I have tried to use the
=AVERAGE(F1:F200) However, when I do this, when I enter a new amount on line
87, the average on line 6 changes also.. What I am after is the average on
line 6 is the average of lines 2-6. The average on line 7 is the average of
lines 2-7. The amount on line 3 should not change once it is figured in. Am I
correct in this thinking? It seems to me that the =AVERAGE(F1:F200) is also
figuring up what is newly entered and recaculating the whole column. I may be
wrong, but that is what it looks like. My question is how do I get the figure
to not change once it is there.. Am I making sense? Thank you in advance for
all of your help and advise.

Scott

 

[Alan]

Thank you to all who replied... thus far this is what I have..

Column A: Date
Column B: Odometer
Column C: Trip
Column D: Gallons
Column E: Price
Column F: MPG
Column G: Avg. MPG

Now I already have Column F: MPG working correctly. I have tried to
use the =AVERAGE(F1:F200) However, when I do this, when I enter
a new amount on line 87, the average on line 6 changes also..
What I am after is the average on line 6 is the average of lines
2-6. The average on line 7 is the average of lines 2-7. The amount
on line 3 should not change once it is figured in. Am I
correct in this thinking? It seems to me that the =AVERAGE(F1:F200)
is also figuring up what is newly entered and recaculating the whole
column. I may be wrong, but that is what it looks like. My question
is how do I get the figure to not change once it is there.. Am I
making sense? Thank you in advance for all of your help and advise.

Try this:

G1 = AVERAGE(F$1:F1)

Copy down

So that, for example, G100 will have the formula:

= AVERAGE(F$1:F100)

As you add new lines, the average will be for the whole dataset, but
the prior (exisitng) averages don't change.

HTH,

Alan.

 

[BenjieLop]

<< Column A: Date
Column B: Odometer
Column C: Trip
Column D: Gallons
Column E: Price
Column F: MPG
Column G: Avg. MPG >>

Honestly, I cannot comprehend why you need a whole column (Column G
for the average MPG. You can have the formula =Average(F1:F200) in jus
one cell. e.g., cell G1.

As you input data in columns A to E, the MPG will be automaticall
calculated in the corresponding cells in Column F. And based on th
calculated values in column F, it should only be cell G1, where th
formula =Average(F1:F200) is entered, that should be changing values.

<< Now I already have Column F: MPG working correctly. I have tried t
use the =AVERAGE(F1:F200) However, when I do this, when I enter a ne
amount on line 87, the average on line 6 changes also.. What I a
after is the average on line 6 is the average of lines 2-6. The averag
on line 7 is the average of lines 2-7. The amount on line 3 should no
change once it is figured in. Am I
correct in this thinking? It seems to me that the =AVERAGE(F1:F200) i
also figuring up what is newly entered and recaculating the whol
column. I may be wrong, but that is what it looks like. My question i
how do I get the figure to not change once it is there.. Am I makin
sense? Thank you in advance for
all of your help and advise.

Scott >>


I really do not understand what you are trying to illustrate here
Perhaps, if you include actual data in your presentation, the analysi
of your problem will be easier

 

[Guest]

That worked wonderfully.. Thank you so much Alan.