Percentage Formula

tylermh ✭✭✭✭✭
edited 09/22/20 in Formulas and Functions

Has anyone built/used a formula to capture the percentage of specific responses in a series of questions?

We're experimenting with Smartsheet Forms to be able to administer an exam. Each column would be a single-select dropdown of the multiple-choice exam question format. On the backend, we want there to be a formula capturing the percentage of correct answers.



  • David Joyeuse
    David Joyeuse ✭✭✭✭✭

    Hi @tylermh

    Never did it, but seems quite doable and would require a little help here and there.

    I would have the first row of your sheet with the right answer for each question.

    Then you're gonna need one helper column for each question to determine he score of each person on each question. You could try to run it with a single formula at once, but depending on the number of question you do have, it would either be one hell of a long huge nested IF statement or a long SUM of COUNTIF, as you'll have to check if the answer is good or not each time (means 1 COUNTIF for every question).

    In your answer column, could be a symbol type column as well, have this formula:

    =IF([Question 1]@row=[Question 1]$1, 1, 0)

    Deploy this for every helper column.

    Then your en formula would be:

    =SUM([Answer Question 1]@row:[Answer Question N]@row)/[Number Questions]

    Have this in a % type column and it'll work.

    Hope I made myself clear enough!

  • tylermh
    tylermh ✭✭✭✭✭

    @David Joyeuse Thank you for the response, David. I do have a couple questions if you're willing to take a look at my sheet? I think it would be easiest to nail this down if you're looking at what I'm looking at. Let me know at and I can invite you to this sandbox sheet I have. Thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!