Discussions

Expand all | Collapse all

Using IF to test multiple conditions

  • 1.  Using IF to test multiple conditions

    Posted 02-15-2019 21:56
    I am building a scorecard where if the value of a field changes to "N/A", I need the total to be divided by a different value (ie: total value - value of field that is set to "N/A"). However, I need the formula to first check if ANY of these 7 fields has a value of N/A, then subtract the values of any fields where the condition is true, and divide the sum of the remaining values by this new "total". If none of the fields has a value of "N/A"). 

    I have set up the fields and form this way:

    • Multiple choice text field to check for Condition, and associated fields for Value of Condition (If or Case formula, depending on # of conditions - Y/N, or Y/N/N/A)
    • A "Subtotal" for groups of Values
    • A Total of all Subtotals
    • Missing: A % Total as described above


  • 2.  RE: Using IF to test multiple conditions

    Posted 02-15-2019 22:11
    In a formula field you can put multiple conditions into your IF statement, eg:

    IF(
    [Field1] = "N/A", [Field2]/5,
    [Field1] = "Yes", [Field2]/3,
    [Field1] = "No", [Field2]/10,
    0
    )

    Basically this says if the value of Field1 is "N/A" then divide Field2 by 5, otherwise if the value of Field1 is "Yes", then divide Field2 by 3, otherwise if the value of Field1 is "No" then divide Field2 by 10.  If all the above are false then make the value 0.

    All of these different criteria can be changed to suit your requirements.


  • 3.  RE: Using IF to test multiple conditions

    Posted 02-16-2019 17:13
    I tried that, but because the values of the "N/A" fields vary (some are worth 2, some worth 5, etc.), and there are 7 of them, once the first condition is met, the form calculates the % based on the Total Value associated with that "N/A" condition and none of the others are calculated.

    What I need is to calculate the Total Value based on the combination of any of the conditions being true.

    Not sure it's even doable at this point :(




  • 4.  RE: Using IF to test multiple conditions

    Posted 02-16-2019 19:03
    You can do an "and" in your query, something like this:
    IF([Field1]="N/A" and [Field2]=5, 20, [Field1]="N/A" and [Field2]=7, 30, [Field1]="Yes" and [Field2]=10, 50, 0) 
    This example sets the value to 20 if Field1 is N/A and Field2 is 5, and sets the value to 30 if Field1 is N/A and Field2 is 7.


  • 5.  RE: Using IF to test multiple conditions

    Posted 02-18-2019 16:16
    I will try that out and let you know, thanks for the feedback!


  • 6.  RE: Using IF to test multiple conditions

    Posted 02-15-2019 23:03
    It's not really clear what you're asking.