formula compilation help

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
I just need some direction on how to compile an if type formula:

I have a questionnaire with a list of approx 20 "Yes" "No" "N/A" options in each (drop down list)

I want to create a quick formula that checks all questions.  If any of the questions have the answer "No" it will give me a "Non-compliance" result, 

and if they are all yes it will give me a "Compliant" result


not sure if this is done with an if statement or not?
Photo of Geoffrey

Geoffrey

  • 1,730 Points 1k badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,756 Points 5k badge 2x thumb
Hi Geoffery,

What happens if some select N/A from the drop-down list? Do you want to show something?

Thanks,
Gaurav
Photo of Geoffrey

Geoffrey

  • 1,730 Points 1k badge 2x thumb
N/A will be the same as "Yes" since it wont have an impact on the final result
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,756 Points 5k badge 2x thumb
Okay. Here you go.

Create a formula-text field and use the below formula.

If([Your drop down field]<>"", 
If([Your drop down field]="No","Non-compliance", "Compliant"),"")
Thanks,
Gaurav
(Edited)
Photo of Geoffrey

Geoffrey

  • 1,730 Points 1k badge 2x thumb
Thanks - How would i stretch this formula across to include all 20 fields?



(Edited)
Photo of Christa

Christa

  • 10 Points

I do something like this assigning a numeric value to the responses and add them together.  Then set up a rule to update your field to compliant as long as this formula field = the total number of questions.


If([Question1]="No",0,1)
+If([Question2]="No",0,1)
+If([Question3]="No",0,1)
+If([Question4]="No",0,1)
+If([Question5]="No",0,1)
+If([Question6]="No",0,1)

Photo of Geoffrey

Geoffrey

  • 1,730 Points 1k badge 2x thumb
Very clever!  This will work!  thanks!