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 doable 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
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!