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

 

[JackDanIce]

In column C, try:

=IF(A1="Chloride",IF(B1>20,000,"OCR",IF(B1<100,"NR",B1)),IF(B1>10,000,"OCR",IF(B1<50,"NR",B1)))

 

[MisterBate5]

Deleted - could not get LT/GT to work properly

 

[Andrew Poulsom]

How about?

=IF(A1="Chloride",IF(B1 > 20000,"OCR",IF(B1 < 100,"ND",B1)),IF(B1 > 10000,"OCR",IF(B1 < 50,"ND",B1)))

 

[Gerald Higgins]

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)))

 

[MisterBate5]

Deleted - again, LT/GT not being interpreted properly.

 

[MrKowz]

Try:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><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 style=";">Nitrate</td><td style="text-align: right;;">25</td><td style=";">ND1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">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 style=";">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 style=";">Nitrate</td><td style="text-align: right;;">11,000</td><td style=";">OCR1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Nitrate</td><td style="text-align: right;;">21,000</td><td style=";">OCR1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Chloride</td><td style="text-align: right;;">25</td><td style=";">ND1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Chloride</td><td style="text-align: right;;">95</td><td style=";">ND2</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">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 style=";">Chloride</td><td style="text-align: right;;">21,000</td><td style=";">OCR2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Sulfate</td><td style="text-align: right;;">25</td><td style=";">ND1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">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 style=";">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 style=";">Sulfate</td><td style="text-align: right;;">11,000</td><td style=";">OCR1</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Sulfate</td><td style="text-align: right;;">21,000</td><td style=";">OCR1</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=IF(<font color="Blue">B1<50,"ND1",IF(<font color="Red">AND(<font color="Green">A1="Chloride",B1<100</font>),"ND2",IF(<font color="Green">OR(<font color="Purple">AND(<font color="Teal">A1<>"Chloride",B1>10000</font>),AND(<font color="Teal">A1="Chloride",B1>20000</font>)</font>),"OCR" & (<font color="Purple">(<font color="Teal">A1="Chloride"</font>)+1</font>),B1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[MassSpecGuru]

In column C, try:

=IF(A1="Chloride",IF(B1>20,000,"OCR",IF(B1<100,"NR",B1)),IF(B1>10,000,"OCR",IF(B1<50,"NR",B1)))

I got an error message with your formula, JackDan, but I couldn't immediately see why, so I tried Andrew's and it worked. Then I compared your formula and his and there isn't any difference except you used "10,000" instead "10000" and I think that extra comma was what was causing problems for Excel with your formula!

Thanks for the excellent and speedy reply!

 

[JackDanIce]

**** typo! Glad you got a solution

 

[MassSpecGuru]

How about?

=IF(A1="Chloride",IF(B1 > 20000,"OCR",IF(B1 < 100,"ND",B1)),IF(B1 > 10000,"OCR",IF(B1 < 50,"ND",B1)))

Thanks, Andrew, as usual your answer worked great! Looking at your construction, even in my sleep-deprived state I can see what I was doing wrong.

Thanks for the speedy reply!