Recently I was asked the question of, “How do I calculate running average on this table of sales data I have?”
Here is the table I was shown:
This is a table showing the Sales and expense figures daily for a company. Column E is a formula which is Sales less Food expense less Labor. Column F is a calculation of Total Revenue (Column E) divided into Total Sales (Column B).
Now column F will just give you the daily average of Revenue divided into sales. The problem is column G, where they want to know the Running Average.
To do this, we need to do a running total of revenue and sales and make a formula to divide them.
So let’s start with this:
We used this formula: =SUM(E3:E3)/SUM(B3:B3)
We are summing up the first day of revenue and divide it into the sum of the first day of sales. Since there’s only one day, that day is also the running total. So you would expect G3 to equal F3.
So now we want to copy this formula down to F4. However, what’s going to happen if we do that?
We would get this formula show up in F4:
And thats not what we want. That is still just giving us the daily average. To get the running average, we need to anchor the first part of our formula.
So we need to add dollar signs in front, like this:
See, now the formula in F4 is =SUM($E$3:E4)/SUM($B$3:B4) which is what we need to see. This gives us the sum of 2 days of revenue and divides into 2 days of sales.
Then when I copy it all the way down, it will keep giving you the running average.
***If you would like more information about Excel functions, check out “Learn Functions Fast” by Chandoo at bit.ly/LearnFunctionsFast