data validated cell

[KBSH]

Hello

I've found something strange concerning data validated cells. In the attachment there's a workbook with row "1" (click on the first sheet....you can ignore the other sheet) containing cells with long formulas. One of those cells has data validation but has a red fill although it only contains a formula and no result. If the result of the formula would be higher then 41 it should mark the cell red and not at this moment.

Does anyone know why the cell is colored red?

[dflak]

The issue is that you are looking at the null string "". Take any cell (say A1) and put ="" in it then evaluate =A1 > 41 and you will see that it evaluates to TRUE. It doesn't make intuitive sense, but that's Excel for you.

So you have to use a formula for the conditional format =AND(H1>41,H1<>"").

[Tony Valko]

So you have to use a formula for the conditional format =AND(H1>41,H1<>"").

Another way...

=N(H1)>41

[dflak]

What is this N formula? I've not seen it before.

[Tony Valko]

From Excel help on the N(value) function...

Data Range

	A	B
1	If value is or refers to	N returns
2	A number	That number
3	A date, in one of the built-in date formats available in Microsoft Excel	The serial number of that date
4	TRUE	1
5	FALSE	0
6	An error value, such as #DIV/0!	The error value
7	Anything else	0

[KBSH]

Thanks guys!

[Tony Valko]

You're welcome!