I'm trying to build a datasheet for work comparing input to multiple conditions and returning a PASS/FAIL. What I started with is this: =IF(ISBLANK(F7),"",IF(ISBLANK(G7),"",IF(AND(OR(F7>10,F7="OL"),G7=$C$86),"PASS","FAIL")))

where $C$86= Mohms.

I wanted to build on this, so I came up with this solution: =IF(ISBLANK(F7),"",IF(ISBLANK(G7),"",IF(AND(OR(F7>10,F7="OL"),G7=$C$86), OR(AND(F7>10000000,G7=$C$84),OR(AND(F7>10000,G7=$C$85),"PASS","FAIL"))))).

where $C$84 = ohms, $C$85= kohms, and $C$86= Mohms.

Excel accepts this formula, however it doesn't work as I intend, and will only show FALSE in the results column.

I want to create a nested statement where I can utilize all 3 units of measurements ($C$s).

What would be the proper way to execute the statement so that my result of <>10,000,000 ohms, 10,000 kohms, and 10 Mohms returns a "PASS"/"FAIL" criteria? Or is it this too arduous of a task for Excel?

CodePudding user response：

Hope it helps as what I understand, copy paste the formula below in cell A7

=IF(F7="","",IF(AND(F7=10,G7=$C$86),"PASS",IF(AND(F7=10000,G7=$C$85),"PASS",IF(AND(F7=10000000,G7=$C$84),"PASS","FAIL"))))

and try to replace the values in cell F7 and G7