Survey Scoring

I have a course evaluation, I want to know how i can write a formula to score the drop down. I.E. Strongly Agree =2 agree =1 neither = 0 disagree = -1 strongly disagree = -2.

I would assume a countif would do it i just don't know how to write it. and i an guessing i would have to add those together and /10 to get the percentage.

@Paul Newcome


  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    This should do the trick.

    =(COUNTIF([Question 1]@row:[Question 10]@row, "Strongly Agree") * 2) + COUNTIF([Question 1]@row:[Question 10]@row, "Agree") - COUNTIF([Question 1]@row:[Question 10]@row, "Disagree") - (COUNTIF([Question 1]@row:[Question 10]@row, "Strongly Disagree") * 2)

  • Kleerfyre
    Kleerfyre ✭✭✭✭✭✭

    The approach I would take is to make a helper column for each Question and then set each column up with a formula that counts for you. Then you can sum those columns and divide that by 10 to get the percentage. You do have to think though, if someone scored all as Strongly Agree, you would have over 100%.

    Column formula: =IF([Question 1]@row = "Strongly Agree", "2", IF(IF([Question 1]@row = "Agree", "1", IF([Question 1]@row = "neither", "0", IF([Question 1]@row = "disagree", "-1", IF([Question 1]@row = "strongly disagree", "-2")))))

    Score Formula: =SUM([Helper1]@row,[Helper2]@row,[Helper3]@row, etc. (will need ten, one for each question)

    At the end of the SUM formula, just put /10 and bingo, should give you what you are looking for.

    Jonathan Sanders, CSM

    "Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭

    With a survey, you would typically look at the average score "per question", but it looks like you are wanting to look at the average score "per submission", is that correct?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!