Conditional formatting icon sets formula bug

BenArnold

New Member
Joined
Dec 13, 2016
Messages
2
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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?

Disable AdBlock

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