How do I get an average based on given month?

SALJ

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?

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

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

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.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.