Monthly Average-Quarterly Average-Yearly Average

Rafafi82

Hello,

I am new to Smarsheet and I am trying to figure out the formula that will get me a Monthly Average, Quaterly Average and Yearly Average of Markups that I have for each person on our Team. If someone could help me, I will really appreciate it. See screenshots examples below:

Thank you!

prime_nathaniel

@Rafafi82

You want to use collections to do this.

1. Break up your date into: Year, month, quarter via year(), month(), and then if then to set quarter based on <3 < 6 < 9 as quarters 1,2,3,4 respectively.

2. Make cross references for the names, months, quarters, years, and mark up counts

3. You can now do a collection for each on your summary.

Monthly
average(collect({markup_count},{user},[Persons]@row,{month},month(today()), {year},year(today()))

Yearly
average(collect({markup_count},{user},[Persons]@row, {year},year(today()))

Quarterly
average(collect({markup_count},{user},[Persons]@row, {year},year(today()), {quarter}, the ifthen you used to calculate quarter)

Solution explained:
A collection is just a subset of records that has been filtered, for example filtering just John One records of this year and this quarter is a collection. Now you can't see a collection in a cell because its more than 1 cells worth of data but you can run average on a collection. So you would encapsulate your collection with average and now you have the average of John One records for this year this quarter.

Rafafi82

@prime_nathaniel

Thank you for your help.

I do have some questions if you don't mind.

• For {user}, I have to reference the "Persons" Column of my "General" Sheet, correct?
• Do I have to create a "Today" Column on my "General" Sheet?
• Where {month} is, I reference the month column I created in my "General" sheet, correct? and then for month, do I change that to the actual month number. For example, I have 3 for March?
• Not sure if after "today()", I have to add any data in the parenthesis?

Thank you, again. I apologize for all the questions.

prime_nathaniel

@Rafafi82

• For {user}, I have to reference the "Persons" Column of my "General" Sheet, correct? - correct
• Do I have to create a "Today" Column on my "General" Sheet? - no today() is its own function
• Where {month} is, I reference the month column I created in my "General" sheet, correct? - this will be the month of date completed
• And then for month, do I change that to the actual month number. For example, I have 3 for March? - the month(date_completed) function will convert this automatically
• Not sure if after "today()", I have to add any data in the parenthesis? - no parameter is needed for this function

Rafafi82

@prime_nathaniel

I keep getting a #INCORRECT ARGUMENT SET" error. Here is how I wrote the formula

Redlines = Markup_count

Store Planners = User

For the Month, I reference the cells with the number 3 from my "General Sheet" (I selected only those cells. I dont know if that makes a difference)

For the Year, I reference the cells with "2025" for the Month of March.

Not sure what I am doing wrong.

Thank you again.

prime_nathaniel

@Rafafi82

Works fine on my side

=AVG(COLLECT({Redlines}, {Store Planners}, Persons@row, {Month}, MONTH(TODAY()), {Year}, YEAR(TODAY())))

Want to make sure you have your {Month} and {Year} correct, see how I have a year column that shows 2025 but if a date completed was 2024 or 2026 it would show that number not just 2025. Same with month, it should show lots of months not just march.

The only other thing is you need person column filled out for each record. This can be done as in my example below OR you can use a parent row and parent formulas to get the value within a child section.

Rafafi82

@prime_nathaniel

Thank you. I will keep trying.

What if I want to do an Average for each month, not just current month. Do I remove "today" from the formula and reference the cells of the month I need only?

prime_nathaniel

@Rafafi82

The correct way to do this scalably is to have a summary with the following columns

Year | Quarter | Month | Person | Monthly Summary

You would then have an individual row for each person for each month

Year | Quarter | Month | Person | Monthly Summary
2025 | Q1 | 1 | John one | #
2025 | Q1 | 2 | John one | #
2025 | Q1 | 3 | John one | #
2025 | Q1 | 4 | John one | #

Now your reference to compare can be set dynamically as Month@row which will return just the month data for month 1, 2, 3, 4 etc.

This structure allows you to do an analysis over any number of months or years

Rafafi82

@prime_nathaniel

It worked!! Thank you so much for your help.

prime_nathaniel

@Rafafi82 you are most welcome