Help with an IF AND formula

[Wayne_Merlino]

Hi,

I am in need of some guidance on a formula. I have two columns that I am comparing against each other - basically determining the dollar difference between the two and then the % difference that amounts to. Once that is figured out, I want to grade the resulting % as pass or fail, but I want to do it as a range. Here is an example:

A) Final Estimate: $10,000.00
B) Preliminary Estimate: $9,000.00
C) Difference in $: ($1,000.00)
D) Difference in %: (10%)

Here is where I need a formula:

IF the result of D is within a range of 10% under to 10% over the Final Estimate then the grade would be “Pass”. IF the result is either more than 10% below or over 10% above the Final Estimate, then the result is “Fail”. So I want to allow a spread of 10% each way off of matching the numbers as allowable.

Any help would be appreciated. Thanks!

[Chris-T]

Hi Wayne. This might work…I haven’t tried it. It should get you close.

IF(
AND({Final Estimate},{Preliminary Estimate}),
IF(
ABS( ({Final Estimate}-{Preliminary Estimate})/{Final Estimate})<=0.1,
‘Pass’,
‘Fail’)
)

Good luck!

[Wayne_Merlino]

Hi Chris,

Here is what I actually have for column names:

Estimate (LU) for the main estimate.
Calculated - ROM Amount for the preliminary estimate.
The column I am using for the formula is called Grade.

Here is what I did related to you solution: Not sure if I am doing something wrong, but it didn’t seem to return the values:

IF(AND({Estimate (LU)},{Calculated ROM}),IF(ABS({Estimate (LU)}-{Calculated ROM})/{Estimate (LU)})<=0.1,‘Pass’,‘Fail’))

AT says there is an error with my formula… did I miss something or write it wrong?

Thanks

[Chris-T]

Hey Wayne. I think you missed a second open paren after “ABS”. Check my formula, there should be two, otherwise it looks good to me. Let me know if that works.

[Wayne_Merlino · Accepted solution]

That was it!! Thanks - never would have EVER got this one without your help!