Results 1 to 9 of 9

Thread: Putting together a logical check with combined conditions

  1. #1

    Putting together a logical check with combined conditions



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,

    I'm not really experienced with excel formulas, and I was hoping someone help find out where I'm going wrong. What I am trying to do is if the three cells C4, H4 and I4, have met the conditions, I would like to apply a value into a cell new cell depending on the value in L28. I have a long list and the value in L28 can differ in value between 1 to over 365.

    =IF(OR($C4="Inactive", $H4="NO",I4="YES"),0,IF(OR($L28<90,$M$6),IF(OR(L28>90,L28<180),$O$6),IF(OR(L28>181,L28<365),$O$6),IF(L28>365,$P$6))

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IF(OR($C4="Inactive",$H4="NO",I4="YES"),0,IF($L28<90,$M$6,IF(L28<180,$O$6,IF(L28<365,$O$6,$P$6))))


  3. #3
    Thank you for the feed back. With my formula I would like to have four options. If the Number in L28, 90 and under, between 91 and 18, been 181and 365, and 365and over. Is there anyway to add this in your formula?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    Those options are in my formula. Please test it with input variations to see how/if it works.


  5. #5
    Hello,

    In the formula above you have options for under 90,under 180 and under 365. I'm looking of an option for (90 and below, 91 to180, 181 to 365 and over 365). Using the formula I only have three options.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    Ok, just change the < to <=

    e.g

    =IF(OR($C4="Inactive",$H4="NO",I4="YES"),0,IF($L28<=90,$M$6,IF(L28<=180,$O$6,IF(L28<=365,$O$6,$P$6))))

    Note: The IF function works on a "stop" when TRUE rule.

    So after it's checked for the C4,H4,I4 checks and resulted that all three are FALSE, then the L28 is checked. It first checks if L28 is less than or equal to 90, if yes, it stops, if no, it checks next if it is less than 180 (it doesn't have to check if it is higher than 90 as it already checked if it was lest than 90, so by default it would be higher than 90. IF TRUE it stops, if not, then it goes to check if it is less than or equal to 365 and uses the same logic as before. Finally if it is not less than 365, it is assumed to be greater, so P6 will return because that is the what if False part.
    Last edited by NBVC; 2014-04-14 at 04:12 PM.


  7. #7
    Okay now I understand, it worked great thank you for all your help!!!

  8. #8
    Quote Originally Posted by NBVC View Post
    Try:

    =IF(OR($C4="Inactive",$H4="NO",I4="YES"),0,IF($L28<90,$M$6,IF(L28<180,$O$6,IF(L28<365,$O$6,$P$6))))
    my friend so I can check where the input variable and add your examples on how I can guide with pictures please? thank you very much

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,463
    Articles
    0
    Excel Version
    Excel 2016
    In your spreadsheet enter anything into cells M6, O6, and P6.

    Put the formula anywhere else, say in Q6. Now put a number in L28. One of the items from M, O or P should now be in the formula cell. Keep changing the number with variations of numbers between 0 and say 500, you should see different results, accordingly.

    After seeing the formula again, I think the OP meant to use N6 for <180. It would make better sense.

    e.g.


    =IF(OR($C4="Inactive",$H4="NO",I4="YES"),0,IF($L28<90,$M$6,IF(L28<180,$N$6,IF(L28<365,$O$6,$P$6))))


Posting Permissions

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