Finding the average

neg177
neg177
in Modeling

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

Tagged:

Best Answer

  • ChrisAHeathcote
    ChrisAHeathcote
    Answer ✓

    @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.

    ChrisHeathcote_0-1619079740823.png

     

    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.

    ChrisHeathcote_1-1619079905049.png

     

Comments

  • jackcplanning
    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
    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
    neg177

    neg177_0-1619039666980.png

     

  • ArunManickam
    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

  • neg177
    neg177

    Hi,

     

    Thank you it worked out great. 

     

     

Categories