Need Help With A Big Formula

[ULFireTester]

Hi, I've got a formula that I'm needing help on.

Here's what I want the end result to be:

• If both G142 & H142 are blank, return “N/A” on V142
• If G142 is blank & N142 is ??, then return “Test Failed” on V142
• If G142 is blank & N142 is <= 250, return “Test Passed” on V142
• If both G142 & N142 are not blank, then calculate the absolute difference between them and if that is <= 30, return “Test Passed” on V142 but if the absolute difference between them is greater than 30, return “Test Failed” on V142.

I'm not even sure if those 4 bullet points are in the correct order or not.

Thank you very much in advance,
Chris B

 

[McGuilliam]

Hi

This formula should work for your requests:

=IF(AND($G$142="";$H$142="");"N/A";IF($G$142="";IF($H$142<=250;"Test Passed";"Test Failed");IF(AND($G$142<>"";$H$142<>"");IF(ABS($G$142-$H$142)<=30;"Test Passed";"Test Failed");"H142 Blank")))

The only thing missing was what you want to happen when H142 is blank but G142 is not. Now I entered "H142 Blank", but feel free to adjust this if needed.

 

[Fluff]

Assuming H142 should have been N142, maybe

=IF(AND(G142="",N142=""),"N/A",IF(G142="",IF(N142<=250,"Test passed","Test failed"),IF(N142<>"",IF(ABS(G142-N142)<=30,"Test passed","Test failed"),"Test failed")))