Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combined IF and OR (Excel 2000-2004)

    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!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combined IF and OR (Excel 2000-2004)

    Does this do what you want:

    <pre>=IF(OR(ISNUMBER(E29),ISNUMBER(F29),ISNUMBER(G 29),ISNUMBER(H29)),(IF(MIN(E29:H29)<TODAY()+LeadTi me,"T","")),"")
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combined IF and OR (Excel 2000-2004)

    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!

  4. #4
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combined IF and OR (Excel 2000-2004)

    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!

  5. #5
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combined IF and OR (Excel 2000-2004)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •