Need help with an average formula
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
Best Answer
-
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"))
Answers
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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.
-
Do you need to add weights to specific columns/questions, or are you wanting to add the weights to the responses?
-
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.
-
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"))
-
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.
-
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)
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives