Conditional Formatting confusion

copperwasher

Board Regular
Joined
Mar 1, 2016
Messages
84
Office Version
  1. 365
Platform
  1. Windows
Hi there Excel Experts,

I am using conditional formatting as a basic setting - as below

if...
number = 1 = yellow
number < 1 = red
number > 1 = green

Sounds simple enough!
So what am I missing here... As this is the output!
E1.PNG


How can the red be shown when the number is 1.00
Red has been set when the number is less than 1.00

The cell is formatted to number - checked

I am always braced for the obvious answer
And willing to learn

My thanks and appreciation
Copperwasher
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
have a look at the value in the cell - and increase the decimal places , it maybe 0.99999999999999999
which is 1.00 to 2 decimal places

you can always test that cell in another cell , assuming the cell is B2
=B2 = 1
you will get a true or false

i always use a formula
select the column then use , say is column B - then formula is
=B1<1
New formula for each rule
 
Upvote 0
Hi
Is that 1st "1.00" calculated? if yes mayne thats not 1.00 but 0.999999... just some formating shows you 1.00
 
Upvote 0
How can the red be shown when the number is 1.00
Red has been set when the number is less than 1.00
That would seem to suggest that while your number is shown as 1.00, it is really less than that, and just rounds to 1.00.

Is the cell that contains the 1.00 hard-coded, or a formula?
If a formula, modify the formula to round it like this:
=ROUND(your formula here, 2)

Likewise, you can also modify your Conditional Formatting rules to apply rounding there before comparing the result to 1.00.
 
Upvote 0
Solution
Hi
Is that 1st "1.00" calculated? if yes mayne thats not 1.00 but 0.999999... just some formating shows you 1.00
Hi there

The number is copied and pasted using JitBit.
The number has been rounded to the nearest 2 digit setting.

If the number is less than 1.00 - it would display 0.99.
And likewise, if the number is more than 1.00 - it would display 1.01

I think I have found a glitch with Excel 365 on the desktop.
Having refreshed the doc, and re-played Jitbit (Using the same data) it has performed as per the conditional formatting.

I'll also take note of the Round formula via @Joe4

Thank you for messaging me, here we are - all learning
Copperwasher
 
Upvote 0
That would seem to suggest that while your number is shown as 1.00, it is really less than that, and just rounds to 1.00.

Is the cell that contains the 1.00 hard-coded, or a formula?
If a formula, modify the formula to round it like this:
=ROUND(your formula here, 2)

Likewise, you can also modify your Conditional Formatting rules to apply rounding there before comparing the result to 1.00.
Hi Joe4

As above @CsJHUN
I think I have found a glitch with Excel 365 on the desktop.
Having refreshed the doc, and re-played Jitbit (Using the same data) it has performed as per the conditional formatting.

I'll apply the Round formula - that is smarter use of excel

Thank you for helping out
Copperwasher
 
Upvote 0
Note that this could be due to the famous "floating arithmetic" situation that occurs due to how Excel stores numbers (so sometimes, depending on how the numbers get into Excel, values like 1.00 may really be stored as 0.999999999999999417, or something like that).

Here is a explanation of the issue (which also show the method I proposed for dealing with it):
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

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