Excel 2007 : Getting IF Logical tests to work and ignoring blanks

[dncvet2004]

This topic may have been asked before so apologies. But I am a novice excel 2007 user.

I have spreadsheet with mastitis cell count data for dairy cows.

In column AH, I want every cell down the rows in that column, to ask the following questions:

IFS((AND(AC2+AD2+AE2>=3, AF2>500, AG2>=1),"CULL"), (AND(AB2+AC2+AD2+AE2=2, 250<AF2<500, AG2>=1), "LA DCT"), (AND(AB2+AC2+AD2+AE2=1, 150<AF2<250), "SA DCT"), (AND(AB2+AC2+AD2+AE2=0, AF2<150), "Teat Seal / No Treatment"))

If the first argument doesn't fit, I want the formula to ask the 2nd argument and so on, the default value if equations aren't met is "No Treatment"

But my formula doesn't work, what am I doing wrong.


Also I want to perform a calculation using a couple of cells, but if the cells concerned are blank, I would like the result to return Unknown.

My current formula is this, but if P2 is blank and w2 is blank it returns the answer no, but I would prefer the answer blank or "unknown" to be written

=IF(AND(150<P2>=250, W2<150),"Yes","No")

Many thanks

[Mallycat]

please post a sample sheet