Solved

Does X = Y within 0.1% variance

Posted on 2016-07-21
8
37 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 14

Expert Comment

by:DrTribos
Comment Utility
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
Comment Utility
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 49

Expert Comment

by:Ryan Chong
Comment Utility
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
 

Author Comment

by:recycleaus
Comment Utility
Wont work as the numbers in C3 to H3 are already slightly rounded. They actually connect to other sheets
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 82

Expert Comment

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

Accepted Solution

by:
DrTribos earned 500 total points
Comment Utility
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
Comment Utility
Worked perfectly DrTribos thanks
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
no wakkas mate, avagoodweekend
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now