Solved

Does X = Y within 0.1% variance

Posted on 2016-07-21
8
51 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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