Skip to main content
Solved

Help with an IF AND formula

W
Forum|alt.badge.img+9

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!

Best answer by Wayne_Merlino

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

View original
Did this topic help you find an answer to your question?

4 replies

C
Forum|alt.badge.img+2
  • Chris-T
  • Inspiring
  • 29 replies
  • July 2, 2021

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!

W
Forum|alt.badge.img+9
Chris-T wrote:

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!


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

C
Forum|alt.badge.img+2
  • Chris-T
  • Inspiring
  • 29 replies
  • July 2, 2021
Wayne_Merlino wrote:

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


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.

W
Forum|alt.badge.img+9
  • Wayne_Merlino
  • Author
  • Inspiring
  • 27 replies
  • Answer
  • July 2, 2021

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

Reply


Most helpful members this week

Terms & Conditions