How do I calculate a Group Average for the Most Recent Date?
I have assessment scores for individuals within different groups (Male or Female). The assessments are taken over time as a sort of "pulse check." Now, I want to be able show a group average (average for all Males and average for all Females) for the most recent round of assessments. How do I calculate that?
I've tried combinations of Max, Ave, and Collect statements, but can't seem to get them to work in the right order. The picture below shows what I'm trying to accomplish.
Any guidance? Thanks in advance.
Best Answer
-
Hi Scott,
You're on the right track with MAX(COLLECT and AVG(COLLECT. First we can use a formula to return the most recent date, with the MAX(COLLECT formula, that looks at the Date Range, then matches the Gender with the gender listed in your GenderAvg column (I've named it this because you can't have two column with the same name)
=MAX(COLLECT(Date:Date, Gender:Gender, =GenderAvg@row))
Then, once you have the dates, you can use this in your next formula, the AVG(COLLECT formula. An AVG(COLLECT works like this:
=AVG(COLLECT([Range to Avg]:[Range to Avg], [Criteria Range 1]:[Criteria Range 1], Criteria 1, [Criteria Range 2]:[Criteria Range 2], Criteria 2...) etc
So in your sheet, try the following:
=AVG(COLLECT(Score:Score, Date:Date, =[Most Recent]@row, Gender:Gender, =GenderAvg@row))
Here are some Help Center articles I used: MAX function / AVG function / Collect function / @row function
Let me know if this works for you, or if you have any other questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi Scott,
You're on the right track with MAX(COLLECT and AVG(COLLECT. First we can use a formula to return the most recent date, with the MAX(COLLECT formula, that looks at the Date Range, then matches the Gender with the gender listed in your GenderAvg column (I've named it this because you can't have two column with the same name)
=MAX(COLLECT(Date:Date, Gender:Gender, =GenderAvg@row))
Then, once you have the dates, you can use this in your next formula, the AVG(COLLECT formula. An AVG(COLLECT works like this:
=AVG(COLLECT([Range to Avg]:[Range to Avg], [Criteria Range 1]:[Criteria Range 1], Criteria 1, [Criteria Range 2]:[Criteria Range 2], Criteria 2...) etc
So in your sheet, try the following:
=AVG(COLLECT(Score:Score, Date:Date, =[Most Recent]@row, Gender:Gender, =GenderAvg@row))
Here are some Help Center articles I used: MAX function / AVG function / Collect function / @row function
Let me know if this works for you, or if you have any other questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Genevieve, Thank you! I've been trying for a few days to get this to work. What I was missing was that piece of referencing the average formula back to the Max Date and the Gender column. It all works now!!! Thank you!
-
Wonderful! I'm so glad to hear it works. :)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!