Conditional Format not working on some values

[GadgetGazza]

I have a worksheet and in column M the formula =SUM(I5:L5) to row I10.

Conditional format "cell value is not equal to =$G$5" then highlight red.

This is fine on a lot of the values from 0 >15 but when the value in column G is 8.5 > 9.75 then it still stays red?

The value is always in 0.25 increments

Column G "=E5" If I change this to the actual number the red highlight clears.

Any ideas?

 

[Joe4]

Welcome to the Board!

Can you tell us what the value are in G5 and E5, and what the values are in columns one of the rows where it isn't working right (column I through L)?

 

[GadgetGazza]

Hi Joe4, Thanks for the reply.

E5 "=SUM(D6-INT(D6))*24" and G5 "=E5"

Columns I through L are just numerical Format number to 2 decimal places

If the total is 7.50 it does not matter if 7.50 is in column I or 5.00 in column I and 2.50 in column J the red highlight does not clear.

 

[Joe4]

OK, you keep adding in new cells to populate that weren't mentioned in the previous post.(D6 now).

Can you to provide us with ALL the pertinent details of an example that doesn't work (that means providing the values in ALL cells pertinent to the example and all the formulas in the in all cells pertinent in the example).

Thanks

 

[GadgetGazza]

Sorry I picked the wrong row. should of been D5.

Complete row is A "Date" B Time "00:00:00" C Time "00:00:00"

column D "=SUM((C5+1)-B5)" (using 24 hr clock gives hours between start B and finish C)

E "=SUM(D5-INT(D5))*24"

Then G "=E5"

Have tried the conditional format looking at G and also E and on both, if the numeric number is there it is ok, but not with the formula.

 

[Joe4]

OK, I do not think you are understanding me. I am trying to reproduce the EXACT situation that you are experiencing where it is not working, so I need the exact details. I want an actual example of one that is not working. Let me know the EXACT value of every single populated cell in row 5.

Or better yet, maybe post a screen print of these values using one of the tools mentioned here: http://www.mrexcel.com/forum/showthread.php?t=508133

 

[GadgetGazza]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
6	05-Sep-11	08:00:00	15:30:00	07:30:00	7.50	1.00	7.50	Monday	7.50				7.50
7	06-Sep-11	04:00:00	16:45:00	12:45:00	12.75	1.00	12.75	Tuesday	10.75	2.00			12.75
8	07-Sep-11	07:00:00	15:30:00	08:30:00	8.50	1.00	8.50	Wednesday	8.50				8.50
9	08-Sep-11	05:30:00	15:15:00	09:45:00	9.75	1.00	9.75	Thursday	9.25	0.50			9.75
10	09-Sep-11	08:00:00	12:45:00	04:45:00	4.75	0.00	4.75	Friday	4.75				4.75
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M6	1. / Cell Value not equal to =$G$6	Abc
M7	1. / Cell Value not equal to =$G$7	Abc
M8	1. / Cell Value not equal to =$G$8	Abc
M9	1. / Cell Value not equal to =$G$9	Abc
M10	1. / Cell Value not equal to =$G$10	Abc

Excel Jeanie works well

This example the person did not work Sunday so no values in row 5.

Row 5 - 11 Sunday - Saturday are all the same.

Thanks for your help.

 

[Joe4]

A few issues:

1. You are not using the SUM function correctly in some of your equations. You use it to SUM up a range of values, like your did in cell M6. But it is unnecessary in cells D6 and E6 (it shouldn't change the results, but it is generally considered bad form and can confuse the situation). They should be able to be written like:
D6: =C6+1-B6
E6: =(D6-INT(D6))*24

2. In copying your formulas and values, I do not get the value you are showing in E6. It returns 12 hours for me which makes sense because (7:30 - 7:00) * 24 returns 12 hours. I do not see where you are getting 7.50 from. What is this value supposed to represent?

 

[GadgetGazza]

Hi Joe4,

As you may of guessed this calculates hours worked and pay required further down the sheet.

As the macros and other formulas to get us here would not work on single minutes we pay to the quarter hour, or in decimal 0.25. (Probably would calculate correctly, just not with the limited knowledge I had when first writing the program)
E6 is the 7.5 hours worked that day. Cell format "number to 2 decimal places"

I is day hours, J is night hours and K is overtime.

Thanks

 

[Joe4]

It seems to work for me.
Where is the value in I6 coming from?