Calculating Moving Average

[sidneyvanness]

We are developing a tiered commission structure for our sales reps whereby the percent commission they earn will be based on the recent moving average of their sales. For example, if salesperson X has a 60 day moving average of $25,000 in sales, they would earn a higher percentage of commissions on all subsequent sales than the person who has a 60 day moving average of only $5000 in sales.

I would like to develop a workbook that can calculate a moving average based on

1. An input variable that specified the number of days over which the moving average should be calculated.
2. An array of dates in one column and
3. A corresponding array of dollar amounts in another.

I’ve not been able to find a clear example of how to do this in Excel…any advice on which functions I should be thinking of?

 

[kieran]

An array function (http://www.emailoffice.com/excel/arrays-bobumlas.html) or the Sumproduct function should be able to accomplish this for you.

Without specifics as to the layoput of your data it is difficult to be more precise.