How can I create formula to only calculate a set if another column is NOT N/A?
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?
Best Answer
-
You would use an IF statement.
=IF(Applicability@row = "N/A", "N/A", [Score Total]@row/30)
Answers
-
You would use an IF statement.
=IF(Applicability@row = "N/A", "N/A", [Score Total]@row/30)
-
Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!