I’m trying to streamline a nested IF statement that my lab uses on a spreadsheet for keeping track of what we make every day for testing – it’s a ridiculously complex statement that reads the value of a cell in column A, compares that value to one of the nested IFs and whether the value in a cell of the same row in column P is within the specified range, then returns “Yes” if the two statements evaluate as true and “No” if not.
This output is then used as part of a conditional formatting formula that will turn the cell red if the value of the nested IF is “No” and green if the value of the nested IF is “Yes”. The formula in question is as follows (and for the record, I didn’t write it, I just inherited it):
=IF(Sheet1!A5=””,””,IF(OR(Sheet1!A5=”Potato Dextrose Agar”,Sheet1!A5=”Sabouraud Dextrose Agar”,Sheet1!A5=”Vegan PDA”),IF(AND(Sheet1!P5>=5.4,Sheet1!P5<=5.8),”Yes”,”No”),IF(OR(Sheet1!A5=”EE Broth”,Sheet1!A5=”R2A Medium”,Sheet1!A5=”Slanetz & Bartley Medium”,Sheet1!A5=”Yeast Extract Agar”,Sheet1!A5=”Cetrimide Agar”,Sheet1!A5=”Harlequin Medium”,Sheet1!A5=”Nutrient TSB”,Sheet1!A5=”Vegitone + Tween”,Sheet1!A5=”Vegitone LB Broth”),IF(AND(Sheet1!P5>=7,Sheet1!P5<=7.4),”Yes”,”No”),IF(OR(Sheet1!A5=”MRD”,Sheet1!A5=”Neutraliser 3″,Sheet1!A5=”Neutraliser 4″,Sheet1!A5=”Neutraliser 5″,Sheet1!A5=”TNC”,Sheet1!A5=”Minimal Agar”,Sheet1!A5=”Minimal Broth”,Sheet1!A5=”Neutraliser 9″,Sheet1!A5=”Vegan TNC”),IF(AND(Sheet1!P5>=6.8,Sheet1!P5<=7.2),”Yes”,”No”),IF(OR(Sheet1!A5=”Neutraliser 1″,Sheet1!A5=”Neutraliser 2″,Sheet1!A5=”Pseudomonous Agar”),IF(AND(Sheet1!P5>=6.9,Sheet1!P5<=7.3),”Yes”,”No”),IF(OR(Sheet1!A5=”Candida Chromogenic Agar”,Sheet1!A5=”Neutraliser 7″),IF(AND(Sheet1!P5>=5.8,Sheet1!P5<=6.2),”Yes”,”No”),IF(Sheet1!A5=”Malt Extract Agar”,IF(AND(Sheet1!P5>=5.2,Sheet1!P5<=5.6),”Yes”,”No”),IF(Sheet1!A5=”Buffered Charcoal Yeast Extract Agar”,IF(AND(Sheet1!P5>=6.8,Sheet1!P5<=7),”Yes”,”No”),IF(Sheet1!A5=”CLED Bevis Modification”,IF(AND(Sheet1!P5>=7.3,Sheet1!P5<=7.7),”Yes”,”No”),IF(Sheet1!A5=”DE Neutralising Broth”,IF(AND(Sheet1!P5>=7.4,Sheet1!P5<=7.8),”Yes”,”No”),IF(Sheet1!A5=”RVS Broth”,IF(AND(Sheet1!P5>=5,Sheet1!P5<=5.4),”Yes”,”No”),IF(Sheet1!A5=”Sabouraud Liquid Medium”,IF(AND(Sheet1!P5>=5.5,Sheet1!P5<=5.9),”Yes”,”No”),IF(OR(Sheet1!A5=”Reinforced Clostridial Medium”,Sheet1!A5=”Tryptone Water”),IF(AND(Sheet1!P5>=6.6,Sheet1!P5<=7),”Yes”,”No”),IF(OR(Sheet1!A5=”MacConkey Agar”,Sheet1!A5=”Mannitol Salt Agar”,Sheet1!A5=”VRBGA”,Sheet1!A5=”Blood Agar”,Sheet1!A5=”XLD Agar”),IF(AND(Sheet1!P5>=7.2,Sheet1!P5<=7.6),”Yes”,”No”),IF(OR(Sheet1!A5=”MacConkey Broth”,Sheet1!A5=”Microbial Content Test Agar”,Sheet1!A5=”Tryptone Soy Agar”,Sheet1!A5=”Tryptone Soy Broth”,Sheet1!A5=”PBS”,Sheet1!A5=”Mueller Hinton Agar”,Sheet1!A5=”Vegan Tryptone Soy Agar”),IF(AND(Sheet1!P5>=7.1,Sheet1!P5<=7.5),”Yes”,”No”),IF(Sheet1!A5=”Middlebrook 7H11 Agar Base”,IF(AND(Sheet1!P5>=6.4,Sheet1!P5<=6.8),”Yes”,”No”),IF(Sheet1!A5=”Neutraliser 8″,IF(AND(Sheet1!P5>=6,Sheet1!P5<=6.4),”Yes”,”No”),”Another Neutraliser”)))))))))))))))))
If there is a way to streamline the above using any other function, especially if it makes it easier to edit the statement to remove data errors (the numbers are pH values and at least one range is not correct), I would welcome the help to implement it – my Excel skills are a little rusty.
Thanks in advance, 😀
