conditional formatting not working properly

[raffexpat]

I have used conditional formatting on an entire column to check a formula. The formula used is "if format is=W9=(P9-E9-Q9)" then format font red. It works for some cells but not others. When I check the same result using =if(W9=(P9-E9-Q9),"OK","Error". It does the same thing. I shows OK for some and notfor others. If I then check the results manually I get the right results for the entire column and it all checks out. With the first option I highlighted all the cells but removed absolute references and for the second method I copied and pasted.

 

[raffexpat]

Has this posting made the problem any easier to understand. Anyone?

 

[konew1]

Have you checked that the numbers are exactly the same. I've suggested it twice and you haven't responded

 

[konew1]

check this

Book2
	A	B	C	D
1	7
2
3
4	0.142857143		1/7
5	1.285714286		9/7
6	1.142857143		A2-A1Expect(9/7)-(1/7)toequal(8/7)
7	6.9999999999999900		1/(A2-A1-1)Expecttheanswertobe7
Sheet1

 

[sbeatton]

Cell C7 states "1/(A2-A1-1) Expect the answer to be 7". Formula is actually =1/(+A5-A4-1)
Results in A4, A5 & A6 are correct, i.e. = 1/7, 9/7 & 8/7 respectively.

 

[sbeatton]

Same formulas and obtain result. Ar you sure A1 is exactly = 7? Also check under Tools, Options, Calculation that "Precision as displayed" is not checked.

Excel Workbook
	A	B
1	7
2
3
4	0.142857	1/7
5	1.285714	1 2/7
6	1.142857	1 1/7
7	7.0000	7
Sheet1

 

[sbeatton]

If "Precision as displayed" is selected, I get the following result as per yours. Reckon that's your problem.

Excel Workbook
	A	B
1	7
2
3
4	0.142857143	1/7
5	1.285714286	1 2/7
6	1.142857143	1 1/7
7	6.9999999930	7
Sheet1

 

[konew1]

My apologies for the details typed into cells C6 and C7. I then insereted a few rows and didn't change the comments. Thanks sbeatton.

Raffexpat - have you tried it yet?

 

[raffexpat]

Hi, I thought I did reply. sorry. the numbers are exactly the same. regards

 

[raffexpat]

My apologies for the details typed into cells C6 and C7. I then insereted a few rows and didn't change the comments. Thanks sbeatton.

Raffexpat - have you tried it yet?

formatted sales.xls
	T	U	V	W	X	Y
8	Option2	Option3
9	33,550		33,550	OK	OK
10	31,600		31,600	OK	OK
11	25,850		25,850	OK	OK
12	35,100		35,100	OK	OK
13	29,025		29,025	OK	OK
14	31,600		31,600	OK	OK
15	25,025		25,025	OK	OK
16	35,625		35,625	OK	OK
17	30,800		30,800	OK	OK
18	30,000
19	31,050
20	35,775
21	32,500
Sheet1 (3)

I am not sure what happened here but it seems to work now without doing a thing. please take a look. sorry to waste your time