?
Solved

Does X = Y within 0.1% variance

Posted on 2016-07-21
8
Medium Priority
?
57 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
[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
  • Learn & ask questions
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 53

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
Technology Partners: 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 84

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 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

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 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