Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Does X = Y within 0.1% variance

Posted on 2016-07-21
Medium Priority
59 Views
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
0
Question by:recycleaus
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 15

Expert Comment

ID: 41724158
Well, you could do this:
=IF(K16/K17 > 1.01, "OOS", IF(K17/K16 > 1.01, "OOS", "OK"))

OOS = Out Of Specification,  OK is within specification
0

Author Comment

ID: 41724163
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
0

LVL 53

Expert Comment

ID: 41724166
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
0

Author Comment

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

LVL 84

Expert Comment

ID: 41724178
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.
0

LVL 15

Accepted Solution

DrTribos earned 2000 total points
ID: 41724199
So, if I understand... you could  (using my original formula instead of this:

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

k17 = ('P&L - All'!Z19)
k16 = SUM(C3:H3)
replace "OK" with  'P&L - All'!Z19
0

Author Closing Comment

ID: 41724216
Worked perfectly DrTribos thanks
0

LVL 15

Expert Comment

ID: 41724219
no wakkas mate, avagoodweekend
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month11 days, 5 hours left to enroll