# Conditional formatting icon sets formula bug

#### BenArnold

##### New Member
Hello,

I've been using Excel for years and this is the first time I've not been able to find an existing solution to a problem - normally on this forum.

I've set up an icon set - four icons.

There are three cells involved:

A2 = Comparator
B2 = Result
E1 = Tolerance

The icon should be green when Result (B2) >= Comparator (A2).
The icon should be amber when Result >= Comparator - Tolerance (in my example: =\$A\$2-\$E\$1)
The icon should be red when Result >=0
The icon should be grey when there is no value entered.

When:
Comparator = 8.7; Result = 8.5; Tolerance = 0.2
The icon is amber - which is correct as the result is within the tolerance range of 0.2.

As soon as you add 0.1:
Comparator = 8.8; Result = 8.6; Tolerance = 0.2
The icon is red - even though it should be amber as 8.8-0.2 = 8.6

You get the same result when the comparator is 8.9 and the result it 8.7, but when you get to 9.0 and 8.8 the icon turns amber.

What is it about the values between 8.8 and 8.9 that it doesn't like? I feel like I'm going slowly mad.

Any help?

Many thanks

Ben

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Eric W

##### MrExcel MVP
Welcome to the forum.

I suspect the issue is that Excel does not store fractional values in decimal, rather in binary. 8.5 in binary might really be 8.4999999999932 and 8.6 might really be 8.6000000000011 depending on how things are rounded. If you change your Amber formula from:

=\$A\$2-\$E\$1

to

=ROUND(\$A\$2-\$E\$1,1)

you might get the result you expect. I tested it and it works as I'd expect. Hope this helps.

Edit: Here are a couple of links that might explain it a bit better:

https://support.microsoft.com/en-us/kb/78113
https://support.microsoft.com/en-us/kb/214118

Last edited:

#### BenArnold

##### New Member
Welcome to the forum.

I suspect the issue is that Excel does not store fractional values in decimal, rather in binary. 8.5 in binary might really be 8.4999999999932 and 8.6 might really be 8.6000000000011 depending on how things are rounded. If you change your Amber formula from:

=\$A\$2-\$E\$1

to

=ROUND(\$A\$2-\$E\$1,1)

you might get the result you expect. I tested it and it works as I'd expect. Hope this helps.

Edit: Here are a couple of links that might explain it a bit better:

https://support.microsoft.com/en-us/kb/78113
https://support.microsoft.com/en-us/kb/214118

You're a genius. Thank you.

It's a weird phenomenon that I haven't quite got my head around yet. When I was trying to figure out what was going on - it's interesting that when I had put 1.3 in A1 and then "=A1+0.1" in cell A2 and filled down, it worked until it got to 5.9, after which point it showed 5.99999999999999. Although when I've done exactly the same thing again in a new workbook, it has worked normally. I can deal with quirks so long as their consistent!

So very confusing.

Ben

#### Eric W

##### MrExcel MVP
You're welcome.

It's an odd thing that still jumps up and surprises me now and again, but I usually remember it quickly nowadays.

Replies
1
Views
211
Replies
3
Views
845
Replies
1
Views
309
Replies
7
Views
1K
Replies
3
Views
718

1,195,699
Messages
6,011,183
Members
441,592
Latest member
Vasant bangalore

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back