recycleaus

asked on

# Does X = Y within 0.1% variance

I would like to know if there is an Excel calculation that asks does X = Y but instead of being an exact match there is a tolerance of say 0.1%.

Thanks

Thanks

ASKER

I perhaps should have explained my requirements better. This is the calculation I have right now.

=IF(('P&L - All'!Z19)=SUM(C3:H3),'P&L - All'!Z19,"ERROR")

I am trying to ensure a group of cells add up to one particular cell but as there are minor rounding differences I am getting errors when really the are acceptable rounding errors and there should be no error. So if I can build in a 0.1% tolerance then there will not be an error.

Thanks

=IF(('P&L - All'!Z19)=SUM(C3:H3),'P&L - All'!Z19,"ERROR")

I am trying to ensure a group of cells add up to one particular cell but as there are minor rounding differences I am getting errors when really the are acceptable rounding errors and there should be no error. So if I can build in a 0.1% tolerance then there will not be an error.

Thanks

for the part:

`('P&L - All'!Z19)=SUM(C3:H3)`

you can do the rounding for comparison!`round('P&L - All'!Z19, 2 ) = round( SUM(C3:H3), 2)`

depends on what precision you want
ASKER

Wont work as the numbers in C3 to H3 are already slightly rounded. They actually connect to other sheets

A comparison to low and high limits is Always two operations, one greater than and one less than. It has to be, there are two different numbers to check against.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Worked perfectly DrTribos thanks

no wakkas mate, avagoodweekend

=IF(K16/K17 > 1.01, "OOS", IF(K17/K16 > 1.01, "OOS", "OK"))

Put your numbers in k16 and k17 and adjust as needed

OOS = Out Of Specification, OK is within specification