Excel question

[IronWing]

I have chemical analylical results stored in two columns, A and B. I want to add them together. Sometimes one or the other number is not a number but an "ND" indicating that the result was below the dection limit for the analyis. In this case I would like the formula to return the value of the chemical that was detected. In some cases, neither were detected in which case I'd like "ND" returned.

The following will return A+B if both A and B are numbers or ND if one or the other is not a number.
=IF(ISERROR(A1+B1),"ND", A1+B1)

Any ideas on how to get the result I'd like?

For bonus cookies, I'd like conditional formatting to change the font color for cells where only one chemical result was a number.

 

[drinkmorejava]

Use nested IF statements.

You would replace your final A1+B1 statement with something like IF(ISERROR(A1),B1,IF(ISERROR(B1),A1,A1+B1))

For the conditional formatting, you would enter a formula exactly the same as you used above, but replace the the "ND" and A1+B1 outputs with 0, and the A1 and B1 outputs with 1.

 

[dave_the_nerd]

You can do that?

Hot diggity.

 

[IronWing]

Okay, I figured out one way to do it.

Formula:
=IF(AND(ISTEXT(A1),ISTEXT(B1)),"ND",IF(ISTEXT(B1),A1,IF(ISTEXT(A1),B1,B1+A1)))

Then for the conditional formatting:
=OR(ISTEXT(A1),ISTEXT(B1))

Thanks for the help drinkmorejava!

 

[Cerebus451]

Use nested IF statements.

You would replace your final A1+B1 statement with something like IF(ISERROR(A1),B1,IF(ISERROR(B1),A1,A1+B1))

For the conditional formatting, you would enter a formula exactly the same as you used above, but replace the the "ND" and A1+B1 outputs with 0, and the A1 and B1 outputs with 1.

ISERROR won't work, because the ND in the column isn't an error. ISNUMBER should work in its place.

 

[IronWing]

ISERROR won't work, because the ND in the column isn't an error. ISNUMBER should work in its place.

Yeah, I figured out the ISERROR part. I had copy/pasted from a old spreadsheet where I was capuring divide by zero errors derived from blank cells.

 

[Tweak155]

Is it a large sheet? If so VBA is a better choice.

 

[ringtail]

use the SUMIF function.

in other words, =SUMIF(F6:F18,"<>ND", F6:F18)