Perhaps you have long suspected that somewhere, concealed deep within the labyrinth of Microsoft Excel, lies an easy way to make your computer translate your O.D’s. into positive/negative values. Unfortunately, that little paper clip guy seems to be clueless about O.D’s., and the dog, cat and robot are no better informed. Well, your suspicions are correct, and the magic words with which to address the Office Assistant are, “IF worksheet function.” If you’re the geeky type, you have probably already dropped this column and set to work reading the help article with a pretty good idea where I am headed. However, if you are smarter than a computer, you are probably still lost, and perhaps feeling some trepidation concerning this “IF worksheet function” business. No need to fear; the IF worksheet function is straightforward. Its purpose is to fill a worksheet cell with one of two possible values based on criteria, which may include calculations performed on other cells. The usage of this function is as follows:
=IF(logical test, “value if true”, “value if false”)
Suppose that we have a column of O.D’s. like those in Figure 1, and suppose that these were generated by the VMRD Anaplasma test kit (Cat. No. 282-2 or 282-5). Positive/negative in this test kit are determined by the % inhibition of each serum sample. The formula for % inhibition is: %I = 100-(Sample O.D. x 100 ÷ NC O.D.). Samples producing a % inhibition less than 30 are negative and those greater than, or equal to, 30 are positive. We can use this information as the logical test for our IF worksheet function, and make the value if true “Neg.” and the value if false “Pos.” Our mean negative control (NC) is located in B2, the first sample we want to consider is B3, and the cell we want to say “Pos.” or “Neg.” is C3. So in cell C3 we begin:
=IF(100-(B3/B2*100)<30,
This is our basic logical test. Now, if the logical test represents a true description of the data, then we want “Neg.” to appear in cell C3, so we continue:
=IF(100-(B3/B2*100)<30,”Neg.”,
The quotation marks surrounding “Neg.” are very important; if we do not include them, the formula will not work. If the logical test evaluates false, i.e., if the % inhibition is greater than or equal to 30, we want cell C3 to say “Pos.”, so we complete the formula:
=IF(100-(B3/B2*100)<30, “Neg.”, “Pos.”)
Note once again the quotation marks surrounding “Pos.”, and the closing parenthesis, “)”, both of which are essential. This formula is good so far as it goes, but to get any utility out of it, we need to be able to copy it to other cells. In its present form, this formula will produce erroneous data if copied to another cell. If we copied this formula to, say, cell C4, B3 in this equation will change to B4 (which is what we want), but B2 in the equation will change to B3, making the % inhibition calculation invalid. To prevent this, we need to change the formula as follows:
=IF(100-(B3/$B$2*100)<30,”Neg.”, “Pos.”)
The “$” before the column and row designators of the negative control cell “locks” them so that they do not change when the formula is copied to other cells. Now that we have correctly composed the formula in cell C3, we can either use the fill handle or copy and paste to propagate our formula from cell C3 to cells C4 through C8. The completed formula and its results are shown in Figure 2.
