Finding the average

Hi,

Need help creating a formula that pick's up the moving average for a number within 6 business days.

Example) The amount for a line item over the past Weekdays were 1, 4, 6, 8, 9. I want to forecast this by finding the average of those days.

Thank you

Accepted answers

ChrisAHeathcote

@neg177

The trick her is to manipulate the value for the days so that you replace the weekend value with the first two values of the moving 5 day average.

Therefore, as the moving average progresses the function includes the weekends but the values for Tues and Weds are posted forward into Sat and Sun so they are included in the movingsum.

Create a new line which I have called, 'Movingsum Value', and using WEEKDAYS to distinguish which day of the week post forward 4 days if the weekday is 6 or 7. This has the effect of replacing Sat and Sun with the values from Tue and Wed.

You can then use the MOVINGSUM function as standard to calculate a moving average.

All comments

jackcplanning

Have you tried using MOVINGSUM? Contrary to the name, it can also be used to calculate the moving average if you indicate the aggregation method.

You can see more on the formula here: MOVINGSUM

neg177

Yes, we are using the Moving sum, but not getting back the correct average, I believe because it is picking up weekends (which we don't want it to do). This is the current formula we are using...

MOVINGSUM('Y03 Securitization (Actuals)'.Portfolio Factor Rate, -5, 0, AVERAGE)

we are trying to pick up the previous M, T, W, Th, F

neg177

ArunManickam

Hello @neg177 ,

You could achieve it still using the movingsum. But do not use the parameter Average.

Sum and divide by the count.

Using if condition, sum only the weekdays (skip the weekends).

XX-Y03 Securitization (Actuals) = if weekend then 0 else Y03 Securitization (Actuals)

For eg, if the current day is monday, your moving sum should be

Average = MOVINGSUM('XX-Y03 Securitization (Actuals)'.Portfolio Factor Rate, -6, 0, SUM) / 6

But you divide it by 6, so you will skip the weekend and calculate your average.

Hope it helps.

Thanks

Arun

ChrisAHeathcote

@neg177

The trick her is to manipulate the value for the days so that you replace the weekend value with the first two values of the moving 5 day average.

Therefore, as the moving average progresses the function includes the weekends but the values for Tues and Weds are posted forward into Sat and Sun so they are included in the movingsum.

You can then use the MOVINGSUM function as standard to calculate a moving average.

neg177

Hi,

Thank you it worked out great.

Quick Links

Can you help?

Unanswered questions

Hide the cards under "Models" section once connected
Hi Is there a way to hide the cards under the "Models" section once connected, as I would like the users to see only the links to the apps? Thank you
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…