Combining Formulas - Average, Collect, Combine, Value, IF Error
Hello, I need help with a formula combing average, value, collect, and contains. I am trying to pull data from another sheet pulling out just the values from the selected options (options are 4 - Complete Success, 3 - Success with Minor Risk, 2 - Success with Major Risk and 1 - Process Breakdown) and then to average them IF the Month and Year meet certain criteria. I cannot figure out where in the formula I am going wrong. Below is the current version of the formula. I have also listed two separate formulas that work, I just can't figure out how to combine them together.
Doesn't work (Invalid Argument):
=IFERROR(AVG(COLLECT(VALUE(IF(CONTAINS("4", {Friendly Greeting}), "4", IF(CONTAINS("3", {Friendly Greeting}), "3", IF(CONTAINS("2", {Friendly Greeting}), "2", IF(CONTAINS("1", {Friendly Greeting}), "1"))))), {Year}, "2023", {Month}, "1")), "-")
Both work:
=IFERROR(AVG(COLLECT({Percentage Score}, {Year}, "2022", {Month}, "12")), "-")
and
=AVG(VALUE(IF(CONTAINS("4", {Friendly Greeting}), "4", IF(CONTAINS("3", {Friendly Greeting}), "3", IF(CONTAINS("2", {Friendly Greeting}), "2", IF(CONTAINS("1", {Friendly Greeting}), "1", ""))))))
Please let me know if you need any additional information from me. Thanks!
Answers
-
If you are just wanting the Average of the Values of the Friendly Greeting Column that have the specified year and month in the same row I believe the below will work.
Create a helper column on your source sheet to get the value for each row that you want to average and use that to get your average.
On your source sheet for the helper column create a column formula of the below
=IF(CONTAINS("4", [Friendly Greeting]@row), "4", IF(CONTAINS("3", [Friendly Greeting]@row), "3", IF(CONTAINS("2", [Friendly Greeting]@row), "2", IF(CONTAINS("1", [Friendly Greeting]@row), "1", ""))))
On your target sheet sheet do
=Iferror(Avg(Collect({Helper column},{Year},"2022",{Month},"12"),"")
-
Thank you for sharing! The helper column was my backup plan so certainly do-able but was trying to see if there was a way to make the formula work instead. If not, agree that the helpers could solve my dilemma.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!