Asked to Calculate Average Monthly Submissions

Meredith Rhodes

Hello!

I had a simple request - to calculate the average monthly submissions. While it's easy for me to actually calculate - I'm having trouble translating the request into a formula to track in the Sheet Summary.

Each row is a request - so I have a 'count' of 111 requests. I have a "Date Submitted" to determine the number of unique months. So - essentially - the number of requests divided by the count of unique months.

How do I write a formula that turns a list of dates into a count of unique months?

Thank you in advance for your help!

Meredith

AravindGP

Hi Meredith,

You can possibly look at just adding a helper column for the date submitted column to identify the month. So, a simple = MONTH([Date Submitted]@row) will bring you a numeric value for the month. January being 1, February being 2, etc. You can then do the formula as =COUNT(Requests) / COUNT(DISTINCT(helper column).

Meredith Rhodes

Thanks @AravindGP - I'd like to be able to ultimately report two things - the number of submissions per month/year (Feb 2022, March 2022) in a graph. I see that Work Insights has something - but I can't seem to select all of the data.

AND craft a formula for the Sheet Summary that simply shows the average number of monthly submissions. Is there a way to collect the oldest and newest dates in a column and calculate the number of months between them?

Meredith Rhodes

UPDATE!

@AravindGP - here is the helper column formula I used (offered to me by Jamie Meyer)

=IFERROR(IF(MONTH([Date Submitted]@row) = 1, "January", IF(MONTH([Date Submitted]@row) = 2, "February", IF(MONTH([Date Submitted]@row) = 3, "March", IF(MONTH([Date Submitted]@row) = 4, "April", IF(MONTH([Date Submitted]@row) = 5, "May", IF(MONTH([Date Submitted]@row) = 6, "June", IF(MONTH([Date Submitted]@row) = 7, "July", IF(MONTH([Date Submitted]@row) = 8, "August", IF(MONTH([Date Submitted]@row) = 9, "September", IF(MONTH([Date Submitted]@row) = 10, "October", IF(MONTH([Date Submitted]@row) = 11, "November", IF(MONTH([Date Submitted]@row) = 12, "December")))))))))))) + " " + YEAR([Date Submitted]@row), "")

I used this new column to create a pivot table to count the number of submissions per month. I was able to manipulate the resultant table to create child rows to create monthly averages for each year.

That formula is key.

AravindGP

That's great! Having all of the values in Sheet Summary might be too much. You can create a separate metrics sheet and have one column with February 2022, March 2022, April 2022, etc. as rows and in the second column have the formula to count the number of submissions against these months. The formula you would use would be =COUNTIF([Helper column]:[Helper column], [Primary Column]@row), assuming you have the months listed in the Primary column. You can then drag this formula down across other month/year combinations to get the monthly submissions.

You can then write a simple average formula across all these values as needed to get the average for the year.

Meredith Rhodes

Thank you for that suggestion @AravindGP -

I will definitely try this approach. Unless it is user error (which is a distinct possibility), the pivot table fails to add zero where there are no submission counts in any given month - this unfortunately effects the averages that it calculates. I made automations on the pivot table to add zero where there are blanks, but that seems like a wonky work-around.