Add point values and weights to answers

I have a Smartsheet form that is populating answers from the individuals who fill out the form. I'd like to do the following three things with the various answers:

  • add point values to answer choices
  • add “weights” to different questions
  • run calculations on a series of questions. For example, if the person put an answer that we deem to be “Very Large Focus”, then the point value for that answer should be 4 x .35 = 1.4

Has anyone done this before or have any insight on what would be the best way to go about this?



  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi there!

    I have done something similar. Here's a brain dump of what you could do:

    For each question, you should have the following columns:




    In this example, let's assume the options are answers A, B, C, D, E, ranging from Strongly agree to Strongly disagree. We want to assign 5 points to A, 4 to B, etc.

    In the Value columns, you'll set up a column formula like this:

    In the Score columns, you'll then have a column formula like this, assuming the weight of that particular question is 0.35:

    You'll then add a set of those columns for each question, setting the column formulas to reference the answer column for that question, and setting the Score column formulas to multiply by the weight of the question.

    I then created a column for total score, summing up the Score columns:

    Hope this helps! Let me know if it works.



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!