Help with IF statement in XL 2003

[MassSpecGuru]

Not sure if it's the lack of sleep or what, but try as I might, I can't seem to get my XL formula to function properly.

What I want to do is have a single formula to evaluate the number in a specific cell (the quantitative result of an analytical test) and, depending on the analyte name (up to 5 different ones) in another cell, "code" the result based on whether it is lower or higher than preset (detection) limits. The problem comes in because one analyte, chloride, has detecton limits that are different than (twice as high as) the other four analytes.

In Column A will be the names of the analytes tested. In Column B will be some number, the quantitated concentration of the analyte in Column A. In Column C should be the "corrected" concentration, based on the rules outlined below.

For all analytes other than "chloride", if the quantitative value is less than 50 (ppb), I want the value in the result column, Column C, to be "ND", and conversely, if the quantitative value is greater than 10,000 (ppb) I want the value in the result column to be "OCR" (over calibration range). Otherwise, the value in Column C should be the same as what is in Column B.

For example:

Column A Column B Column C
Nitrate 25 ND
Nitrate 95 95
Nitrate 750 750
Nitrate 11,000 OCR
Nitrate 21,000 OCR

However, if the analyte is "chloride," the "ND" limit should be 100, and if it's over 20,000, then it should be coded "OCR." Anything else should just return the number.

For example:

Column A Column B Column C
Chloride 25 ND
Chloride 95 ND
Chloride 750 750
Chloride 11,000 11,000
Chloride 21,000 OCR

I can write an Excel formula using a couple nested IF statements for each of the analytes alone, but I cannot seem to combine them into one formula that functions properly.

When I use the below formula, everything works fine EXCEPT I get the "over calibration range" error message when Chloride is over 10,000, not 20,000. (Note: In the formula below, I modified the "ND" and "OCR" messages slightly in an effort to see where my formula is falling apart, and like I said, it's when the Chloride is between 10,001 and 20,000.)

=IF(B1<50,"ND1",IF(AND(A1="Chloride",B1>20000),"OCR2",IF(AND(A1="Chloride",B1<100),"ND2",IF(B1>10000,"OCR1",B1))))

Column A Column B Column C
Nitrate 25 ND1
Nitrate 95 95
Nitrate 750 750
Nitrate 11,000 OCR1
Nitrate 21,000 OCR1
Chloride 25 ND1
Chloride 95 ND2
Chloride 750 750
Chloride 11,000 OCR1
Chloride 21,000 OCR2
Sulfate 25 ND1
Sulfate 95 95
Sulfate 750 750
Sulfate 11,000 OCR1
Sulfate 21,000 OCR1

Can anyone help either fix my formula or come up with an entirely different one that works properly?

Thanks a million,
MSG

 

[MassSpecGuru]

Not the most elegant solution probably, but does this give you the results you want ?

=IF(A2="Chloride",IF(B2<100,"ND",IF(B2>20000,"OCR",B2)),
IF(B2<50,"ND",IF(B2>10000,"OCR",B2)))

Works like a champ, Gerald! Thanks!!

 

[MassSpecGuru]

Try:

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Nitrate</TD><TD style="TEXT-ALIGN: right">25</TD><TD>ND1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>Nitrate</TD><TD style="TEXT-ALIGN: right">95</TD><TD style="TEXT-ALIGN: right">95</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Nitrate</TD><TD style="TEXT-ALIGN: right">750</TD><TD style="TEXT-ALIGN: right">750</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>Nitrate</TD><TD style="TEXT-ALIGN: right">11,000</TD><TD>OCR1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Nitrate</TD><TD style="TEXT-ALIGN: right">21,000</TD><TD>OCR1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>Chloride</TD><TD style="TEXT-ALIGN: right">25</TD><TD>ND1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>Chloride</TD><TD style="TEXT-ALIGN: right">95</TD><TD>ND2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD>Chloride</TD><TD style="TEXT-ALIGN: right">750</TD><TD style="TEXT-ALIGN: right">750</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold">Chloride</TD><TD style="TEXT-ALIGN: right">11,000</TD><TD style="TEXT-ALIGN: right">11000</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD>Chloride</TD><TD style="TEXT-ALIGN: right">21,000</TD><TD>OCR2</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD>Sulfate</TD><TD style="TEXT-ALIGN: right">25</TD><TD>ND1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD>Sulfate</TD><TD style="TEXT-ALIGN: right">95</TD><TD style="TEXT-ALIGN: right">95</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">13</TD><TD>Sulfate</TD><TD style="TEXT-ALIGN: right">750</TD><TD style="TEXT-ALIGN: right">750</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">14</TD><TD>Sulfate</TD><TD style="TEXT-ALIGN: right">11,000</TD><TD>OCR1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">15</TD><TD>Sulfate</TD><TD style="TEXT-ALIGN: right">21,000</TD><TD>OCR1</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>C1</TH><TD style="TEXT-ALIGN: left">=IF(B1<50,"ND1",IF(AND(A1="Chloride",B1<100),"ND2",IF(OR(AND(A1<>"Chloride",B1>10000),AND(A1="Chloride",B1>20000)),"OCR" & ((A1="Chloride")+1),B1)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Works great, MrKowz! Many thanks to you, too for the speedy response!

 

[MassSpecGuru]

Man, is everybody here today on caffeine but me, or what?

These are some really fast replies, guys!

I owe you all a beer!

Many thanks,
MSG

 

[MrKowz]

Man, is everybody here today on caffeine but me, or what?

This board runs on caffeine, or in the case of some users gin or scotch.