Help with Formula for formatting, multiple ifs

[flyingfree]

I have a fairly large set of data. 70+columns, 100 + rows. First column is a label, the second and third columns have some values in them and some blanks. There are both different limits on what the data in the rest of the columns (site data) should be before a warning is issued (format the cell a different fill colour). Some of the data has numbers with a < sign in front of the number. I am looking to check if either of the first two columns has a number, if no fill green, if yes and value in specific row of site data is less than both fill green, if more than one red, if within 10% of one yellow. Some of the limits only exist for one limit.
Specifically I am looking at water quality data with a health limit and a aesthetic limit. Some of the chemicals only have one, some have both. Not even sure where to start.
Perhaps a series if conditional formatting applied in order?
=if(b2="", green)
=if(c2="",green)
=if(d2<b2,green)
=if(d2<c2,green)
=if(d2>.1b2,yellow)
=if(d2>.1c2,yellow)
=if(d2>b2,red)
=if(d2>c2,red)

But how to incorporate the issue of the values that have the < in front of them without a dummy column using the LEFT command to get rid of it? The test results report any number that is less then the detectable limit as <DL (ie. <0.001) not 0. It would work if the condition of the arguement understood every value having a < in front of it was 0.

 

[AlanY]

would you post a small sample of the date here

 

[flyingfree]

MAC	AO	Site 1	site 2	site 3	site 4	site 5
	100​		11	9.9	9.7	130
6​			<0.50	<0.50	<0.50	<0.50
10​	5​		<1.0	<1.0	<1.0	<1.0
2000​			22	21	21	32

I think I got it figured out. Using AND(isblank(a2),isblank(B2) to green the cells that don't have a MAC or AO. Then a whole slew of other arguments in a specific order to get the other cells coloured right. Came to me in the morning. The idea that I don't need a single statement to cover all possibilities with conditional formatting and that I can arrange them in an order. The CELL CONTAINS < argument got all those cells dealt with.