How do I get an average based on given month?

Trying to get the average of [Page Views] for a given month [Last Activity].

Averaging the Total Page Views for the month of September

The following returns invalid data type...

=AVG(COLLECT([Total Views]4:[Total Views]55, [Last Activity]4:[Last Activity]55, =MONTH(9)))

My assumption is that the test for the given month is not matching the expected Last Activity data format.

Thoughts?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @SALJ

    You will want to make sure that the [Last Activity] column is a Date Column type. If it is, then sometimes wrapping an IFERROR statement around the MONTH function can help... try this:

    =AVG(COLLECT([Total Views]4:[Total Views]55, [Last Activity]4:[Last Activity]55, IFERROR(MONTH(9), 0)))


    Let me know if this works for you!

    Cheers,

    Genevieve

  • SALJ
    SALJ ✭✭

    Hi,

    Thanks for the suggestion but now I get a #Divide By Zero error.

    It appears that there is a function/data type mismatch.

    If I insert the Today(-5) function the average is returned, but I cannot use the Month() function.

    =AVG(COLLECT([Total Views]4:[Total Views]55, [Last Activity]4:[Last Activity]55, TODAY(-5)))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @SALJ

    My apologies! I completely missed how the MONTH was set up.

    In a MONTH function you need a date within the parentheses, and then you say if that date = 9 for September:

    MONTH(@cell) = 9

    Try this:

    =AVG(COLLECT([Total Views]4:[Total Views]55, [Last Activity]4:[Last Activity]55, IFERROR(MONTH(@cell), 0) = 9))


    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!