Combined IF and OR (Excel 2000-2004)

Topic

New Reply

I have list of medical supplies that have various expiration dates. With help from the experts here, I created a formula to check for expirations based on a variable called LeadTime that I use for ordering replacement supplies. I would like to extend that to 4 columns of dates that represent separate drawers of suppllies with identical parameters except for the expiration date. I’m sure I need to add an OR function, but I can’t seem to get it in the right place. The original equation that gives me a “T” flag for expiring items in a single cell is:
=IF(ISNUMBER(H29),(IF(H29<TODAY()+LeadTime,"T","")),""). The ISNUMBER is to check for a date, since some items do not expire and the text "None" appears in the cell, and this suppresses value errors. The cells of extra dates in this case would be E39, F39, and G39 (as well as H39).
Thanks for any help!

Reply | Quote

Replies

Does this do what you want:

=IF(OR(ISNUMBER(E29),ISNUMBER(F29),ISNUMBER(G29),ISNUMBER(H29)),(IF(MIN(E29:H29)<TODAY()+LeadTime,"T","")),"")

Reply | Quote

Yes it does! Thanks for such a quick and clever way to do it! I had not even thought about the MIN of a range. I really appreciate the help!

Reply | Quote

Legare,
I forgot to mention something I’ve noticed a couple of times and might be helpful to others. When the solution equation is imbedded in an e-mail, symbols like “<" sometimes come through looking like "&lt" and it takes a little thought to figure out what's wrong if you just copy and paste into Excel. Of course, if you take the trouble to log on to the forum, it displays correctly and can be copied off. I use a Mac with OS 10.3.9 for my e-mail and much of my work, so I'm not sure if it is the Mac mail program or something that happens in transmission. Just thougth others might experience the same thing.
Thanks again for the help!

Reply | Quote

Well, obviously it is an equivalent expression here, because there is no diffference in the displayed symbol in my comments, although I typed it in exactly as it comes through. In my e-mail program it is “ampersand lt”, instead of the less than operator, and it does not work when I cut and paste the equation to Excel2004. Just a little “gotcha” I guess.
Gloria

Reply | Quote