• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

Does X = Y within 0.1% variance

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
recycleaus
Asked:
recycleaus
1 Solution
 
DrTribosCommented:
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
 
recycleausAuthor Commented:
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
 
Ryan ChongCommented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
recycleausAuthor Commented:
Wont work as the numbers in C3 to H3 are already slightly rounded. They actually connect to other sheets
0
 
Dave BaldwinFixer of ProblemsCommented:
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
 
DrTribosCommented:
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
 
recycleausAuthor Commented:
Worked perfectly DrTribos thanks
0
 
DrTribosCommented:
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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now