# give a drop down value a percentage

Options
✭✭✭

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 ??

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

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

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭
Options

thank you Jeff reisman that was perfect

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!