Skip to main content

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

😎 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!

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 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


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.

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

Reply


Terms & Conditions