Averaging Over Date Range

Hello,

I'm attempting to create a monthly scorecard based on an average completion %. The first picture conveys the completion % for that inspection based on inspection form entries, however I need to average that score over the month.

To avoid an extensive formula, I was able to create a formula to count the first part of the date range which I would then use to divide by the sum of the % complete in that same date range which would then give me an average but I'm unable to complete the <=DATE(2022, 4, 31) portion with out an error.

If someone could provide some assistance in creating either a single long formula to account for averages on a monthly basis, or a way to complete the formula I've started, it would be tremendously helpful.

Any help would be greatly appreciated!

Best Answer

  • Bryan Letourneau
    Answer ✓

    Hi Jeff,

    The formula is referencing the date hidden column as it seemed the easiest way to convert the automated Created column to and easy format for reference.

    But that formula worked perfectly, thank you!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @Bryan Letourneau Which of those two date fields is the {Cafe and Breakroom Range 34} range referencing? That may be your issue.

    That being said, you can do this with one formula too:

    =AVERAGEIF({Date Column Range}, MONTH(@cell) = 4, {% Complete Column Range})

    English: Find the rows with dates in the month of April, and give me the average of the % complete column for those rows.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Bryan Letourneau
    Answer ✓

    Hi Jeff,

    The formula is referencing the date hidden column as it seemed the easiest way to convert the automated Created column to and easy format for reference.

    But that formula worked perfectly, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!