Create Rolling Average Formula?

4.17K viewsFormulas and Functions

A beta user posed a question about how to construct a Quantrix Formula that could do a rolling average. That is, they had interest rate value for each period in their model, and they wanted each period to have a rolling average of the last 3 period’s rate values.

It works best to sometimes just write out what you want the formula to do, and then write the actual Quantrix formula.

So basically we want the formula to do something like this:

“THIS periods Rolling Average equals the AVERAGE of the PREVious 3 periods Rate Values”

So, the Quantrix formula version of these words are:

Period[THIS]:Rolling Average=average(Period[PREV-2]:Rate Value .. Period[PREV]:Rate Value)

We are using the THIS and PREV statements, and the average of the PREV-2 through PREV periods gets the rolling average of the last 3 periods.

Download and open up the attached model file for an example. Notice if you add periods, or even add another Category for Scenarios, the one formula calculates it all!



That’s cool, thanks.