give a drop down value a percentage
I have a form with a number of questions (columns) that have drop down's with answers to pick. poor, fair, good, exhalent,
I need to be able to work a percentage and average base base on these answers
I need each to have a individual numeric value poor 25% fair 50% good 75% Exhalent 100%
Eg if all 12 questions were answered exhalent they would = 100%
is this possible and how ??
Best Answer
-
Make sure your question columns are all together in your sheet. Each COUNTIF in the formula below counts the number of cells in the range from Question column 1 to Question column 12 that equal each choice. The "poor" answers get multiplied by .25, the "fair" answers by .5, etc. All the values are added and divided by 12 to get the percentage. (Format the column containing this formula as percent, and the result will be converted into percent automatically. For example, .523 becomes 52.3%)
=((COUNTIF([Question1]@row:[Question12]@row, "poor") * .25) + (COUNTIF([Question1]@row:[Question12]@row, "fair") * .5) + (COUNTIF([Question1]@row:[Question12]@row, "good") * .75) + COUNTIF([Question1]@row:[Question12]@row, "excellent"))/12
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
Make sure your question columns are all together in your sheet. Each COUNTIF in the formula below counts the number of cells in the range from Question column 1 to Question column 12 that equal each choice. The "poor" answers get multiplied by .25, the "fair" answers by .5, etc. All the values are added and divided by 12 to get the percentage. (Format the column containing this formula as percent, and the result will be converted into percent automatically. For example, .523 becomes 52.3%)
=((COUNTIF([Question1]@row:[Question12]@row, "poor") * .25) + (COUNTIF([Question1]@row:[Question12]@row, "fair") * .5) + (COUNTIF([Question1]@row:[Question12]@row, "good") * .75) + COUNTIF([Question1]@row:[Question12]@row, "excellent"))/12
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
thank you Jeff reisman that was perfect
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!