Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Survey Scoring

    ✭✭✭✭✭
    in Formulas and Functions

    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

    image.png


    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • Community Champion

      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)

      · Share on FacebookShare on Twitter
    • Community Champion

      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."

      · Share on FacebookShare on Twitter
    • Community Champion

      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?

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions