Average Collect formula
I'm trying to collect the average survey response number for a quarter for the current year and multiply it by 0.2, for a percentage, but it's coming back unparseable.
The first referenced column is a 1-5 number, the months are from a numerical month helper column, 'Created' is the auto-create date. Any ideas?
=AVERAGE(COLLECT({My provider treated me with respect}, {Month}, 4, {Month}, 5, {Month}, 6, YEAR({Created}), YEAR(TODAY()))) * 0.2
Best Answer
-
I expect it's the YEAR({Created}) part that's causing the error, but I do wonder if the way you're handling months also needs fixing, as no row is going to be all three months at once.
I would change it to:
=AVG(COLLECT({My provider treated me with respect}, {Month}, OR(@cell=4, @cell=5, @cell=6), {Created}, YEAR(@cell)=YEAR(TODAY()))) * 0.2
Note you might want a helper column for the year of your Created column, at which point you would remove YEAR(@cell)= and swap out {Created} for your {Created year helper} column.
I also just noticed that you're using AVERAGE, not AVG, which could also be causing your error.
Answers
-
I expect it's the YEAR({Created}) part that's causing the error, but I do wonder if the way you're handling months also needs fixing, as no row is going to be all three months at once.
I would change it to:
=AVG(COLLECT({My provider treated me with respect}, {Month}, OR(@cell=4, @cell=5, @cell=6), {Created}, YEAR(@cell)=YEAR(TODAY()))) * 0.2
Note you might want a helper column for the year of your Created column, at which point you would remove YEAR(@cell)= and swap out {Created} for your {Created year helper} column.
I also just noticed that you're using AVERAGE, not AVG, which could also be causing your error.
-
This worked! Thank you so much!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!