Scoring Formulas

Amelia C. Thibault

I'm looking to convert a process to smartsheet, but am trying to determine if there is a formula to make the concept work. Currently I have a form which asks a series of questions - each question has a single select option of "yes (description)" "no (description)" and "n/a". For each question, there is a score associated with the answer.

I successfully have this formula working: =IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", "1", "0") So essentially if someone selected "yes" to this column question, I have a separate column which will report a score of 1. If someone selected no or n/a, it will report a score of 0. That part seems to work. Ultimately I'll have 20 or so questions/column entries on a row, with 20 or so matching column entries coding the responses as 1 or 0. For that row, I then need to count each total of 1 or 0 for a total score (5, 17, who knows).

From there I'll be pulling parts of the data out into a report.

Currently though I haven't been able to find a way to count the various columns reporting a 1 or 0 function to give me a total numeric function.

Brett Wyrick

Actually, @Amelia C. Thibault, I figured out why it's not working in the "SUM" formula Parker recommended above. Your formula has quotes around the numerical values, and Smartsheet is reading those as text values.

Here's your formula:

=IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", "1", "0")

And here's what it ought to be:

=IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", 1, 0)

Try that and let us know if the SUM() function works after that!

‎

Parker Oxford

Hi @Amelia C. Thibault

The SUM function should be able to achieve what your intending. You can use something to the effect of the formula below to sum up multiple cells in a row. This should be able to apply as a column formula as well. Just change the column names to whatever you called the columns where the formula lives.

=SUM([Question Answer formula1]@row,[Question Answer formula2]@row...)

Amelia C. Thibault

@Parker Oxford Thank you - unfortunately that doesn't work. When I apply =SUM([Receivership Pre Risk]@row, [Disbarment Pre Risk]@row) it provides me an answer of 0, even though in =IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", "1", "0" my column reads "1" and my Disbarment pre risk reads 0. I should be getting a 1, not a 0. Even if I change all of my values to 1 and add every one to the SUM formula, the answer still calculates at 0.

Brett Wyrick

Hey Amelia.

Seems like Parker's solution ought to work - could you upload a screenshot?

Brett Wyrick

Actually, @Amelia C. Thibault, I figured out why it's not working in the "SUM" formula Parker recommended above. Your formula has quotes around the numerical values, and Smartsheet is reading those as text values.

Here's your formula:

=IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", "1", "0")

And here's what it ought to be:

=IF([Receivership Pre]1 = "Yes, the subrecipient is in receivership", 1, 0)

Try that and let us know if the SUM() function works after that!

‎

Brett Wyrick

PS you could use a CONTAINS function to make this easier for Yes/No responses. Something like:

=IF(CONTAINS("Yes", [Receivership Pre]@row), 1, 0)

Amelia C. Thibault

@Brett Wyrick Thank you!