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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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

    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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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

    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!

  • MATAYLOR
    MATAYLOR ✭✭✭

    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!