Average calculation conditional on Field Name

Question

Hi there,

I have the attached the excel file which represents profit generated with clients. What I need is always the average of the last three actual months. In the example, the 2018-Mar-FC is the first month with an forecasted number (FC=Forecast, ACT=Actual). In order to forecast, I would need to calculate the average of the last 3 actuals months (here in the example Dec17, Jan18 and Feb18. In the next month, the column for March will change to 2018-Mar-Act, hence I would need the average for 2018-Jan-act, 2018-Feb-act and 2018-Marc act etc. Any good ideas?

Thanks, SVM

Book1.xlsx

derekbelyea · Accepted Answer

@svm  This example should move you towards a solution.  As you can see here I suggest that you separate the actual and forecast values and rotate the actuals data so you can use the MULTI ROW FORMULA tool.

BenMoss · Accepted Answer

I appreciate @derekbelyea's solution works perfectly but I just wanted to give you a tip on efficiency.

In the solution Derek has used the same transformation several times (multi row), against different columns. Whenever you find yourself doing this in most instances it is possible to work with your transposed data where all these values are in a single column and 'group by' the header name field, which means the caclulation is made against each of your 'columns' but in a single tool.

I've attached a version with this alternative stream.

Ben