How can I create formula to only calculate a set if another column is NOT N/A?

KarenTF

A form was created to collect a scoring of 1-3 for 10 different items. The goal is to collect the values (SUM) and divide by the highest total score (of 30) to get an overall adherence score percentage. This was originally achieved and work by using a helper column to SUM all 10 values into the Score Total column using the formula =SUM([Value 1]@row:[Value 10]@row

Next the Adherence Score % column was created by using the formula =[Score Total]@row/30 and made the distinction to show the percent. This was working fine for what I needed.

Here's the dilemma: for some folks submitting, this does not apply to them. And I have an Applicability column created for those people to select N/A. The problem is, is that now the adherence score will show as 0% for those individuals. But I'd like for it to show "N/A" in the Adherence Score % column.

Question - is there a way to create an IF statement that will input "N/A" in the Adherence Score % column if the Applicability column is N/A, and if otherwise, to use the calculation above to genereate the score?

I've tried several options, but keep getting errors. Any ideas on where I'm going wrong or if this is even possible?

Paul Newcome

You would use an IF statement.

=IF(Applicability@row = "N/A", "N/A", [Score Total]@row/30)

Paul Newcome

You would use an IF statement.

=IF(Applicability@row = "N/A", "N/A", [Score Total]@row/30)

KarenTF

Thank you!

Paul Newcome

Happy to help. 👍️