Help with formula

[Charlie]

This is a time and conditional format problem I'm having.

X3 has a fixed time value of 0:16
X4 has a fixed time value of 0:30
Y3 has a fixed time value of 0:31

The cells in columns AB6 through AB300 have this formula:
=(if(t6>0,if(u6-t6>0,(u6-t6)-ab3,""),""))

(I can improve this formula later. The formula takes the difference between
two times. I want to use conditional formatting to change the color of the
cell depending on the results: Green if the difference is between X3 and X4
& Red if the difference is equal to or greater than Y3)

I have two conditions set for cells AB6 through AB300:
1st is set at = Cell Value is greater than or equal to =$Y$3, then (pattern)
is red
2nd is set at = Cell Value is between =$X$3 and =$X$4

Problem: When a cell value in AB6 through AB300 is null (except for the
formula) I get a red cell. I'm expecting no conditional formatting under
this situation.

After this problem is solved, how would I write this formula in cells X6
through X300?
(In cell X6) =if(AB6=(red),1,"")

Thanks in advance for help on this one...
Charlie

 

[Trevor Shuttleworth]

Charlie

there was a similar question asked recently in microsoft.public.excel:

Need Conditional Formatting Help

I suggested using:

Formula is: =AND(ISNUMBER(G5),G5>=0,G5<50)

and you should be able to modify this to your requirements.

Frank Kabel suggested:

Formula is: =AND(G5<>"",G5>=0,G5<=49)

It's the test for number or blank that's important

You'll need something like:

Formula is: =AND(AB6<>"",AB6=$Y$3) format as red pattern

See Chip Pearson's site for working with colours.

http://www.cpearson.com/excel.htm

http://www.cpearson.com/excel/SortByColor.htm

Regards

Trevor