Percentage tolerance

[squirrel32]

Hello,

I've been struggling on this for a while,

I have an Aim and CurrentValue and a tolerance of 5%.

I am struggling on how to make a formula which makes it if the current is + or -5% of the Aim it says Within Tolerance (if Current = Aim it says 1)
Points of note, The aim is a static manually added number whilst CurrentValue sums up everthing in I:I

Example:

Aim: 1000
Current: 500
Boundary is:
- 950
+ 1050
Within Tolerance?: Yes, No, 1

In the example, since the current isn't within the boundary it would be No.

Sounds easy enough, I just can't figure it out in my tired state.

[Olly]

Try:

Please Login or Register  to view this content.

See attachment for example.

[samba_ravi]

try
=IF(ABS(aim-current)<=aim*5%,"Yes","No")
=IF(ABS(A1-A2)<=A1*5%,"Yes","No")
A1 and A2 are your aim and current values

[AlanY]

with aim in B1;

=IF(ABS(1-(SUM(I:I)/B1))<=0.05,"YES","No")

[squirrel32]

I used Olly's and it works lovely. I'll have to learn how you did the names as references instead of A1 etc one day

Everyone else thanks :D

edit; Just realised what Olly did after a quick proper glance. Thanks again!

[Olly]

Here's a primer on named ranges

https://support.office.com/en-gb/art...ad=GB&fromAR=1