# Formula to change value to a number, then SUM

Options

Hello! I am struggling with this formula & determined I need to start from the beginning. I have created a quiz. It is a mix of multiple choice (A,B,C,D) and True/False (T,F). Each letter will have a number value which is then SUM'd for a final grade. Something like this:

Question 1: A=5 / B=4 / C=3 / D=2

Question 2: A=0 / B=5 / C=3

Question 3: T=1 / F=0

Any suggestions?

Tags:

• ✭✭✭✭✭✭
Options

Is there a reason you have different numbers for the same letter depending on the question? It would be MUCH easier if each letter was ALWAYS the same number. Otherwise you are going to have to rewrite the formula for every single row.

• Options

Thanks Paul! That is what I was afraid of.

Since the grade is determined by the highest score, if "A" was the same number (highest value), would mean "A" would have to always be the correct answer.

However, I am open to other options --- anything that could make this easier.

• Options

@Paul Newcome What if I have a separate sheet with the answer mapped to a value, something like this:

What is the formula to match the answer selected in the Primary sheet to the value assigned in the separate sheet?

• ✭✭✭✭✭✭
Options

I think I misunderstood your original post. Regardless of the solution, you will need a separate formula for each column. Not a separate formula for each row.

If you were to create a table, the formula would look something like this...

=INDEX(COLLECT({Values Column}, {Question Column}, @cell = "Q1", {Answer Column}, @cell = [Q1]@row), 1)

You would just need to update both of the Q1 to Q2 or Q3.

If you did not want a table, you would use nested IF statements.

Q1 Score:

=IF([Q1]@row = "A", 5, IF([Q1]@row = "B", 4, IF([Q1]@row = "C", 3, IF([Q1]@row = "D", 2))))

Q2 Score:

=IF([Q1]@row = "A", 0, IF([Q1]@row = "B", 5, IF([Q1]@row = "C", 3)))

Q3 Score:

=IF([Q1]@row = "T", 1, 0)

Then you could add them all together to get the final score without needing a helper column for each:

=IF([Q1]@row = "A", 5, IF([Q1]@row = "B", 4, IF([Q1]@row = "C", 3, IF([Q1]@row = "D", 2)))) + IF([Q1]@row = "A", 0, IF([Q1]@row = "B", 5, IF([Q1]@row = "C", 3))) + IF([Q1]@row = "T", 1, 0)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!