# How do I get an average based on given month?

Options
✭✭

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?

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭
Options

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)))

• Employee
Options

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