give a drop down value a percentage

MATAYLOR

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

Jeff Reisman

@MATAYLOR

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

Jeff Reisman

@MATAYLOR

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

MATAYLOR

thank you Jeff reisman that was perfect