Conditional Format not behaving

[a.t.brooks]

Hello
I have a workbook consisting of multiple sheets.
Each sheet, a part from the first, contains data about an experiment
that has been run.
The first sheet is a summary sheet (set out as a table) with
references to data in the other sheets. I'd like conditional
formatting to color text red when certain values are exceeded.
This works for most cells, however, I have one set of values where
conditional formatting won't behave. I have set the cell to show text
color of red when the value > 3 but all values are appearing red.
I think the issue is down to the fact that I have used the RIGHT
function to select the number as the actual value in the reference cel
(B24) is "Std: 1.62" (i.e. letters and numbers). On my summary sheet I
have referenced it as =RIGHT('Sheet2'!$B$24,4). Hence 1.62 will appear
in my cell IN RED!!!
I have checked and the Category is set to Number/2 digits.
Is this a bug in Excel or am I missing something fundamental.
Thanks in advance
Tony

 

[Pete_UK]

RIGHT returns a text value, not a number - change your formula to
something like this:

=RIGHT('Sheet2'!$B$24,4)+0

which will convert it to a number.

Hope this helps.

Pete