rolling 12 month formula

System
System Employee
in Formulas and Functions
This discussion was created from comments split from: Need Help with Formula for Rolling Metrics.
· Share on FacebookShare on Twitter

Answers

  • Tina Davis
    Tina Davis ✭✭✭

    I need help with a average rolling 12 month formula. I can get the 6 month rolling to work as stated below, however I am struggling with a 12 month. Any help would be appreciated.

    =ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 6, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 6, 1))), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Have you tried just using a total of 365 days (or 183 in either direction)?

    =ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= TODAY(183), @cell >= TODAY(-183)), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Vivien Chong
    Vivien Chong ✭✭✭✭✭✭

    @Tina Davis I am thinking would it work if we replace the 6 with 12? I didn't test this formula yet.

    =ROUND(AVG(COLLECT({Data days from requested to received}, {Data test fit received}, AND(@cell <= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1)) - 1, @cell >= IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) - 12, 1), DATE(YEAR(TODAY()) - 1, MONTH(TODAY()) + 12, 1))), {Data design resource used}, "SDCM", {Data test fit approval status}, "Approved")), 1)

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Vivien Chong Maybe a +11 and -1.

    If you go with 12 in either direction, you'll never get a valid month number.


    @Tina Davis Taking a closer look at your formula, I realize you are basically going back 6 months. My formula would be for previous 6 months through next 6 months. If you just want to go back a year, that would look more like


    DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), 1)


    This would go inplace of the IFERROR/DATE/DATE you have set for the greater than or equal to portion in your formula.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Tina Davis
    Tina Davis ✭✭✭

    Thank you so much Paul, I was getting a divided by zero that I am looking at my data for accuracy now.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!