0

I'm setting up an excel sheet to help me with data visualisation. In the picture below you can see cells G, H and I are formatted so that all the values not between the specified threshold should be red. Can anyone see where I'm going wrong?

I tried to format cell to be numbers. I made sure the minus symbol is an actual minus.

  1. I expect the output cell I24 to be red, but it isn't. Cell G28 is working as expected.
  2. If I change the threshold to 0.01 the only cell in red is I28 (it doesn't count values that are 0.01). 0.009 returns pseudo-correct (it didn't returned negative 0.009.

enter image description here

enter image description here

Dominique
  • 16,450
  • 15
  • 56
  • 112
Damio
  • 3
  • 6
  • 1
    it's probably a rounding thing. What happens if you display `I24` with more decimal places? I suspect you'll find it's `-0.0089999...` – Nick Sep 11 '19 at 09:29
  • 1
    I guess I don't see what the issue is. Note that if a cell value reads 0.009, the actual number might differ (e.g. perhaps it is 0.009000001). The best solution might be to add some cushion to the endpoints. – John Coleman Sep 11 '19 at 09:34
  • Try adding a `ROUND` function to your calculations in columns G, H, I . Something like `=ROUND(yourcalculation;3)` will round the real value to 3 decimals, so your CF rule will work as expected. – Foxfire And Burns And Burns Sep 11 '19 at 09:45
  • Nick - It is the rounding thing however I don't understand why; my calculation ARE only to 3 decimal places. I have attached new picture showing 10 or more digits after decimal place. – Damio Sep 11 '19 at 12:27
  • See [Is floating point math broken?](https://stackoverflow.com/q/588004/4996248). When you type `0.009` into Excel, you are thinking about a number which can be represented exactly in 3 decimal places. Under the hood, it is interpreted as a 64-bit floating point number which is only *approximately* 0.009. This is because in base 2 the rational number 9/1000 can't be expressed with a finite decimal expansion (since 1000 is not a power of 2). In base 2, a number like 9/1000 works more like 1/3 does for us: 1/3 = 0.33333.... forever. When you truncate it -- you get round-off error – John Coleman Sep 11 '19 at 13:05
  • I used =ROUND and it seems to work. – Damio Sep 11 '19 at 13:08
  • If you are happy with `=ROUND()`, use it -- but you might be throwing away information that in some other context will be important. In the long term, it might be better to become comfortable with the quirks of floating point round-off error – John Coleman Sep 11 '19 at 13:41

1 Answers1

0

I had to add ROUND in front of my formula and also I had to change conditional formatting to be "format cells greater or equal to" and/or "format cells lower or equal to"

Damio
  • 3
  • 6