7 Day Average/30 Day Average

I am trying to build a formula for a data set that I can calculate as a complete average, but I'd like the 7 day average and the 30 day average.

Here's the formula that works: =AVG([% Successful]:[% Successful])

I know I will need to use AVERAGEIF, but can not get the date parts correct.


Thanks for your help!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Amy Knoll

    Since you have a date range (ex. from a certain date to a certain date), we can actually use an AVG(COLLECT formula to specify more than one criteria.


    Try something like this:

    =AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7)))


    This looks for a date that's less than or equal to Today, but that is greater than or equal to 7 days ago. For your 30 day average, you can swap out the -7 to be -30.

    If you're getting a divide by 0 error, this means that the answer is 0. You can wrap an IFERROR Statement around it to return 0 instead:

    =IFERROR(AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7))), 0)


    Is this the average you were looking for? If you want to specify an exact date instead of TODAY you can use the DATE function, like so:

    [Date Column]:[Date Column], <= DATE(yyyy, mm, dd)... etc

    Cheers!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Amy Knoll

    Since you have a date range (ex. from a certain date to a certain date), we can actually use an AVG(COLLECT formula to specify more than one criteria.


    Try something like this:

    =AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7)))


    This looks for a date that's less than or equal to Today, but that is greater than or equal to 7 days ago. For your 30 day average, you can swap out the -7 to be -30.

    If you're getting a divide by 0 error, this means that the answer is 0. You can wrap an IFERROR Statement around it to return 0 instead:

    =IFERROR(AVG(COLLECT([% Successful]:[% Successful], [Date Column]:[Date Column], <= TODAY(), [Date Column]:[Date Column], >= TODAY(-7))), 0)


    Is this the average you were looking for? If you want to specify an exact date instead of TODAY you can use the DATE function, like so:

    [Date Column]:[Date Column], <= DATE(yyyy, mm, dd)... etc

    Cheers!

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Amy Knoll
    Amy Knoll ✭✭✭

    This worked PERFECTLY. Thanks so much for the help!!

  • I'm so glad! Thanks for letting me know. 🙂

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!