Need help with an average formula

JD Hearen

Smartypants :),

I need some help. I'm trying to average client responses to my form and add a percentage to the total response. I have 52 (maybe more later) questions per client that only have 3x responses, Yes, In-progress/updating, and No. I need to add weight to them and average the responses and "Score" each client 0-100%.

This is the formula I'm using :

=IFERROR(COUNTIF([Account Playbook]@row:[Client communication is being sent]@row, "Yes") / COUNTIF([Account Playbook]@row:[Client communication is being sent]@row, OR(@cell = "Yes", @cell = "No", @cell = "In-progress/updating")), "")

Help please. I'm still a noob to SmartSheets, but I learn fast.

JD

Paul Newcome

Ok. SO to apply the weights, we would count and then multiply by the weights.

=COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "Yes") * 10

=COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "In-Progress") * 5

=COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "No")

Now that you have your counts you can get a total score or an average score.

Total:

=(COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "Yes") * 10) + (COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "In-Progress") * 5) + COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "No")

Average:

=AVG(COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "Yes") * 10, COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "In-Progress") * 5, COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "No"))

Bassam Khalil

Hi @JD Hearen

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

JD Hearen

I can't leave the sheet itself (client info), but I can provide a screenshot. I take the whole row and try and average it to make a score based on hte answers. the goal is to have all the columns say "yes" and the score will adjust to 100% when all is green.

Paul Newcome

Do you need to add weights to specific columns/questions, or are you wanting to add the weights to the responses?

JD Hearen

I'd like to use: Yes = 10, In-progress = 5, and no = 0.

I have another formula that we use for another sheet that does weights for a formula, but I cannot recreate it here. I tried several times and versions. I'll include it for reference too.

=IFERROR((COUNTIF([System ready for QA]@row:["Job box" Prepared for customer]@row, "Yes") + COUNTIF([Final Room Clean]@row:[Final Ready for Cust.]@row, "Yes") + COUNTIF([Safe Install]@row:[Fusion VTC test]@row, OR(@cell = "Pass", @cell = "Completed"))) / (COUNTIF([System ready for QA]@row:["Job box" Prepared for customer]@row, OR(@cell = "Yes", @cell = "No")) + COUNTIF([Final Room Clean]@row:[Final Ready for Cust.]@row, OR(@cell = "Yes", @cell = "No")) + COUNTIF([Safe Install]@row:[Final Room Clean]@row, OR(@cell = "Pass", @cell = "Completed", @cell = "Fail", @cell = "Not Complete"))), "")

For this formula, it's pretty much the same thing, the client fills out a form and the responses "score" the space. It works just fine on that sheet.

Paul Newcome

Ok. SO to apply the weights, we would count and then multiply by the weights.

=COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "Yes") * 10

=COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "In-Progress") * 5

=COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "No")

Now that you have your counts you can get a total score or an average score.

Total:

=(COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "Yes") * 10) + (COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "In-Progress") * 5) + COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "No")

Average:

=AVG(COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "Yes") * 10, COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "In-Progress") * 5, COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "No"))

JD Hearen

I never thought of using a AVG before, Thanks! This mostly works. It will give a large number when I have a lot of "yes" answers. I'd ideally want to have a percentage about (0-100%).

I'll continue to play with formula, because I bet it's something really simple that I'm missing.

Paul Newcome

To get a percentage, we would need to do things just a little differently...

We would take the total and then divide it by the largest possible total which would be the count of all columns multiplied by 10 such as below (also note the extra parenthesis isolating the COUNTIFS + COUNTIFS + COUNTIFS section as well).

=((COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "Yes") * 10) + (COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "In-Progress") * 5) + COUNTIFS([Account Playbook]@row:[Client communication is being sent]@row, "No")) / (COUNT([Account Playbook]@row:[Client communication is being sent]@row) * 10)