The IF function: Excel’s saving grace
I sometimes think that if it weren’t for conditional logic, Excel would be practically useless. I use Excel alot, just about every day. And just about every day, it aggravates me. But then there are the times when I figure something out, some cool way to convert numbers or perform a calculation, and Excel has redeemed itself. And so goes the love/hate relationship. The love is usually brought on by my using some conditional logic to get a job done. The other day I came across the AND function, which basically allows you to test to see if a condition or set of conditions is true or not. I needed to test for multiple conditions in an IF function, and the AND function did it for me.
My data (see screenshot) had two fields (A and B) that I needed to compare, with my result of the comparison going in column C. If A was greater than B, then put A in C (C5). Simple enough. However, there were some caveats. In columns A and C, I had VOID and (BDL) values. If column B=VOID or B=no value, then my column C value needed to be -9999.0 (to one decimal place). If B=(BDL), then C=<0.01. And sometimes I had no values at all (A12, B12). So you can see where I needed the test for multiple conditions. This was what I came up with:
=IF(AND(A5>B5,B5<>"",A5<>"(BDL)",A5<>"VOID"),A5,IF (B5="VOID",FIXED(-9999,1,TRUE),IF(B5="", FIXED(-9999,1,TRUE),IF(B5="(BDL)", "<0.01",B5))))- Download this code: excel-if.txt
The AND allows me to see whether or not multiple conditions are met inside of my logical test for my IF statement. Pretty slick I thought. Now I of course realize that I could have just went through my sheet line by line and manually done this, replacing numbers as I go. I chose not to do that for two reasons: First, I try to never alter my source data, that way I can always go back and look at my original numbers and have confidence in them, and second, what kind of fun would that be? None, of course. Right?
