Excel: Average formula question
Thread Starter
Join Date: Mar 2003
Location: here and there
Posts: 105
Likes: 0
Received 0 Likes
on
0 Posts
Excel: Average formula question
Ive got a very simple spreadsheet that I'm using to calculate my cars fuel consumption (don't ask ).
I'd like to have a single cell that works out the average fuel consumption automatically, is this possible. I've searched Google but cant seem to find the answer, although I may not be quite the right search words there!
At the moment I'm manually editing the formula every time I add another line (ie its something like =SUM(F2:F8)/7 )which is giving me the average of the cells. Is there any way I can get Excel to automatically change the numbers as and when I enter a new line.
Hope that makes sence.
I'd like to have a single cell that works out the average fuel consumption automatically, is this possible. I've searched Google but cant seem to find the answer, although I may not be quite the right search words there!
At the moment I'm manually editing the formula every time I add another line (ie its something like =SUM(F2:F8)/7 )which is giving me the average of the cells. Is there any way I can get Excel to automatically change the numbers as and when I enter a new line.
Hope that makes sence.
Hovering AND talking
Join Date: Feb 2003
Location: Propping up bars in the Lands of D H Lawrence and Bishop Bonner
Age: 59
Posts: 5,705
Likes: 0
Received 0 Likes
on
0 Posts
You can use the Excel help to get the syntax of the AVE function and select a range that is a few lines bigger than you require. Then insert lines above the end of the range as you need to add more data.
Cheers
Whirls
Cheers
Whirls
Spoon PPRuNerist & Mad Inistrator
There's a really simple way to do this: use the Average autofunction, it's in the same location as the Sum function - ∑ sign - just use the drop down list.
SD
SD
Official PPRuNe Chaplain
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
If you're looking to enter fuel purchases and mileage each time, then you can easily have a "rolling cumulative" average fuel consumption.
Just add up all the fuel added (use the SUM function) in gallons, and divide into the difference between latest mileage and "start" mileage.
I have a simple spreadsheet on my iPaq that does this very thing (in Excel).
There are blank lines for future entries, with an "IF" clause that leaves the average consumption column blank if there is no entry in the "fuel loaded" column for that line.
Just add up all the fuel added (use the SUM function) in gallons, and divide into the difference between latest mileage and "start" mileage.
I have a simple spreadsheet on my iPaq that does this very thing (in Excel).
There are blank lines for future entries, with an "IF" clause that leaves the average consumption column blank if there is no entry in the "fuel loaded" column for that line.
Join Date: Jun 2001
Location: Intentionally Left Blank
Posts: 382
Likes: 0
Received 0 Likes
on
0 Posts
Isn't this just a case of using the COUNTIF and SUMIF formulas?
Use COUNTIF to count the number of cells with a value greater than zero; use the SUMIF to SUM the cells if they are greater than zero and then divide the two? (=SUMIF(range,criteria)/COUNTIF(range,criteria)
Use COUNTIF to count the number of cells with a value greater than zero; use the SUMIF to SUM the cells if they are greater than zero and then divide the two? (=SUMIF(range,criteria)/COUNTIF(range,criteria)
Join Date: Sep 2001
Location: UK
Posts: 741
Likes: 0
Received 0 Likes
on
0 Posts
A simple answer for your simple spreadsheet !
It sounds like you have something like this :
If you are going to add more data in cells G2 to G8 and want to perform the same calculation ( SUM ) as you did in Column F, then just Copy and Paste the sum across ( Right click in cell F9 and click Copy, then Right Click in the new cell - G9 and click Paste ) :
and Excel will automatically change the SUM to perform the same calculation on the data in your new Column :
Job done
It sounds like you have something like this :
If you are going to add more data in cells G2 to G8 and want to perform the same calculation ( SUM ) as you did in Column F, then just Copy and Paste the sum across ( Right click in cell F9 and click Copy, then Right Click in the new cell - G9 and click Paste ) :
and Excel will automatically change the SUM to perform the same calculation on the data in your new Column :
Job done
Join Date: Jul 2006
Location: Chasing Dreams
Posts: 221
Likes: 0
Received 0 Likes
on
0 Posts
I know this is a little late, but the way I would approach this is to have a complete column available for data then in a neighbouring column put the formula as shown below:
Say using column D, enter this formula in E2:
Sum(D : D)/COUNT(D : D)
Add whatever you want into the D column and it will continuously update the average.
Say using column D, enter this formula in E2:
Sum(D : D)/COUNT(D : D)
Add whatever you want into the D column and it will continuously update the average.