Combining Formulas - Average, Collect, Combine, Value, IF Error

Daniel.W

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!

Hollie Green

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"),"")

Daniel.W

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.