running averge

I searched this but didn't find the right answer. It's very simple but I don't know formulas well.

I want to keep a running average, meaning as i add new values (in this case my fuel mileage per gallon) i want to know the average of my previous averages. Right now i use =average(a1,a2). but i want a formula that will average a1, a2, a3, a4, a5 etc as i add them so that i don't have to manually rewrite the formula. I know simple enough.

Thanks.

In B1 (for example) put this formula

Please Login or Register  to view this content.

and drag it down. That formula checks the cell to the right (say A15) and if there's a number entered there, calculates the average from A1 to A15.

ChemistB

Thanks! I never would've figured that out. Do you think you could break down the syntax a little more so i can fully understand?

Sure, There's two parts to the equation, the actual average formula and an IF formula. We'll look at the IF formula first. The format for the IF is =IF(expression, is true, is false). Excel looks at the expression and puts one thing in the cell if it's true and a second thing if it's false. So;

Please Login or Register  to view this content.

looks in cell A1 and if there is a number there, it calculates the average, if not it leave it blank ("").

The second part of the formula is the average function. =AVERAGE(RANGE) You want the range to change from row to row. A range can be cells separated by a comma (as you did initially) or by placing a colon between the first and last cell. Thus A1:A5 is the same as A1,A2,A3,A4,A5. The range A1:B2 is A1,B1,A2,B2.

When you drag a formula, the cells automatically update to the new cells (Otherwise, you'd have to manually type each individual formula in each cell). The $in front of a cell lock that part of the cell in place ($A1 locks the column, A$1 locks the row, $A$1 locks both) so that when you drag it, that reference remains constant.

Soooo, as we drag that formula down the rows, the $A$1 remains constant and the A1 changes with each row. So in cell B5 for example, it's going to read

Please Login or Register  to view this content.

and so on. Knowing how to use locked or fixed references and relative references is a huge tool in writing Excel formulas.

Any questions?

ChemistB