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?
I don’t understand what you’ve mentioned. I’m sure it’s 100% correct – I just can’t visualize the equation.
You’re subtracting a DATE from ANOTHER date? Or are you figuring out the number of days BETWEEN two days?
The reason this entry hits me today is because not 6 hours ago I was toying with an Excel formula that would give me all the current days of the current week. An Excel calendar, if you will.
Interesting. I love Excel tips.
Matt,
Basically, it gets the day number from the date string (when the date is in field A10 in this case), so like this:
for 5/13/2001, it gets the day, 13
for 12/5/2003, it get the day, 5
It esssentially plucks the day digits from the date. I’m using the MID function combined with some IFs to see how many characters are in the date and month, then performing whatever needs to be done depending on if the month/day are one or two characters in length. Plug her into a sheet and take her for a test drive (there may be some spaces to remove if you copy/paste, otherwise the code wouldn’t wrap on the page for me).
For some stuff I do, I need to know the day of the month for many years worth of records. This formula grabs it and tosses it into a field for me, with mimimum effort.
And yeah, I love Excel tips too. Crazy formulas can be hard to visualize; some of mine are 2-3 rows long in the Excel formula editor sometimes, and I get lost in them. But when you get them to work, it’s pretty sweet. My girl is a whiz at this stuff and loves to try and figure stuff out for me.
I love Excel. I’ll say it again. I love Excel.
But you and I, and your girlfriend… we see the DYNAMIC side of Excel, whereas 95% of Microsoft Office users only see the “simple spreadsheet” side of Excel.
It is amazing what it can do. And hey- I recently had an entry about how much I couldn’t stand Microsoft products – which you commented on… But Excel is in a class of it’s own – it is the one thing Microsoft did right!
It’s so powerful… and then when you combine it with Access…. good night! You can actually have Access TALK to Excel, and vice versa.
Great stuff…
I’m always bitching about Microsoft products, but when you figure out how to really tap into the power of Excel, and then toss in some VBA, it’s amazing what you can get it to do for you.
I was looking for information on how to do this myself and there is a much simpler way.
Enter the formula =Day(cell number)
Works for me!
Damn Wesley, you’re right! Wow. Much easier. I think I’m just a geek who likes to use IF functions to try and do everything. Thanks for the input.
Greetings,
I copied and ran the formula through. However, it did not work for me. When I entered 1/1/2001, I got 92 in the cell!?!?!? I can’t understand what went wrong. I placed the date 1/1/2001 in cell A10, and the IF statement in cell B10. Any help would be appreciated.
Regards,
Eric