Percentage Formula

tylermh

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

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

@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 tylermh@gmail.com and I can invite you to this sandbox sheet I have. Thank you so much!