Help with Formula for formatting, multiple ifs

flyingfree

New Member
Joined
Mar 2, 2011
Messages
8
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.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,234
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
would you post a small sample of the date here
 

flyingfree

New Member
Joined
Mar 2, 2011
Messages
8
MACAOSite 1site 2site 3site 4site 5
100​
119.99.7130
6​
<0.50<0.50<0.50<0.50
10​
5​
<1.0<1.0<1.0<1.0
2000​
22212132

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,800
Messages
5,574,399
Members
412,590
Latest member
Velly
Top