Excel: Average formula question

[Practice Auto 3,2,1]

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.

[Whirlygig]

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

[Saab Dastard]

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

[bnt]

The Average function is just called =AVERAGE()
Averages over a range, which expands if you insert lines inside the range.

[Keef]

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.

[Icarus]

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)

[Coconutty]

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

[Practice Auto 3,2,1]

Thanks for all your help folks!

Sorted now

[Jimmy Macintosh]

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.