Need to average with multiple criteria - Avg(Collect... not working!

Hello, I am trying to average scores in a column when criteria is met in two other columns. I know SS does not support AverageIfs, so I used AVG(Collect…. It did return a calculation, but the figure is not correct when compared to a report that has the same data or when I manually calculate the figure.
Formula used
=AVG(COLLECT({Audit - Process Score}, {Project Year}, [Primary Column]@row, {Audit Calc}, "1"))
Data set for 2024 (year is primary column)
Score | Audit | Year |
---|---|---|
100.00% | 1 | 2024 |
100.00% | 1 | 2024 |
80.00% | 1 | 2024 |
100.00% | 1 | 2024 |
100.00% | 1 | 2024 |
80.00% | 1 | 2024 |
The average of these six scores is 93.33% when calculated manually and on a report I have for a different purpose.
When I use the Avg(Collect… formula the figure returned is 84.29%.
Any idea why there is a discrepancy or how to fix? Thank you!
Answers
-
It's possible you have a mix of numbers and text in the data, and you're matching an unexpected set of rows. By putting a quote around the "1" in your criteria, you're forcing COLLECT to only match text values of 1. My guess is that in your detailed data you might have a mix of numbers and text values.
Try removing the quotes from your COLLECT and see what you get.
If you're in a situation where the column is going to have a mix of text and numbers all the time, you could add a helper column that uses VALUE to extract the number from text so you can match the number every time. Alternatively you can leave the quotes in the COLLECT and add a helper column that adds +"" to the value to make it text every time.
Also, for troubleshooting you can see what numbers are getting matched by replacing AVG with JOIN. That will show you a text list of values that are being collected for averaging, so you can see what may be getting included that you don't expect.
=JOIN(COLLECT({Audit - Process Score}, {Project Year}, [Primary Column]@row, {Audit Calc}, "1")),",")
-
Thanks for your help Brian!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives