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
  • Learn & ask questions
Solved

Does X = Y within 0.1% variance

Posted on 2016-07-21
8
47 Views
Last Modified: 2016-07-22
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
Comment
Question by:recycleaus
8 Comments
 
LVL 15

Expert Comment

by:DrTribos
ID: 41724158
Well, you could do this:
=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
0
 

Author Comment

by:recycleaus
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 51

Expert Comment

by:Ryan Chong
ID: 41724166
for the part:
('P&L - All'!Z19)=SUM(C3:H3)

Open in new window

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

Open in new window

depends on what precision you want
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

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

Expert Comment

by:Dave Baldwin
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

by:
DrTribos earned 500 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

by:recycleaus
ID: 41724216
Worked perfectly DrTribos thanks
0
 
LVL 15

Expert Comment

by:DrTribos
ID: 41724219
no wakkas mate, avagoodweekend
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question