+ Reply to Thread
Results 1 to 6 of 6

formula IF AND OR combination

  1. #1
    Forum Contributor
    Join Date
    03-28-2017
    Location
    Thailand
    MS-Off Ver
    2013
    Posts
    241

    formula IF AND OR combination

    Hi can someone help me out please, tried different formulas none give the right result

    the result depends on 2 cells P13 and P27,
    P27 "working" "lunch"or "" (empty)
    P13 can be "FREE" or "BUSY"
    the result gets value copied in P13 (macro)
    conditions :
    P13 P27 Result
    FREE (empty) FREE
    FREE working BUSY
    FREE lunch FREE
    BUSY (empty) BUSY
    BUSY lunch FREE

    The result gets a value copy paste in cell P13 (by macro on timer)

    formulas I wrote :
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    best ones working where :
    Please Login or Register  to view this content.
    this one works good except condition 2, cause if he change P13 free to trade then te same formula change back to free

    In fact what I want is : if p27 change to working, he put "BUSY" and hold this until "lunch" come.
    if so : change BUSY to FREE
    sometimes "lunch" comes without a "BUSY" before, he should keep on FREE

    Great thanks for help me out
    Chris
    Last edited by incobart; 10-20-2017 at 05:07 AM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: formula IF AND OR combination

    P13 P27 Result
    FREE (empty) FREE
    FREE working BUSY
    FREE lunch FREE
    BUSY (empty) BUSY
    BUSY lunch FREE
    FREE = FREE (empty) OR FREE lunch OR BUSY lunch
    BUSY = FREE working OR BUSY (empty)

    using those values and not simplifying

    p13 p27

    FREE = OR ( AND (P13="FREE",P27=""), AND(P13="FREE", P27= "lunch") , AND(P13="BUSY",P27="lunch") )

    BUSY = OR ( AND ( P13 ="FREE" , P27="working"), AND(P13="BUSY" , P27=""))

    combine into a Nested IF

    =IF ( OR ( AND (P13="FREE",P27=""), AND(P13="FREE", P27= "lunch") , AND(P13="BUSY",P27="lunch") ) , "FREE", IF ( OR ( AND ( P13 ="FREE" , P27="working"), AND(P13="BUSY" , P27="")) , "BUSY" , "not met criteria" ))
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    10-12-2015
    Location
    Bristol, UK
    MS-Off Ver
    Office 365
    Posts
    39

    Re: formula IF AND OR combination

    What result do you want for P13 = BUSY and P27 = working?

    If it should be BUSY, then
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If it should be FREE, then
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: formula IF AND OR combination

    Here's another option:

    =IF(OR(P13&P27={"FREE","FREElunch","BUSYlunch"}),"FREE","BUSY")

  5. #5
    Forum Contributor
    Join Date
    03-28-2017
    Location
    Thailand
    MS-Off Ver
    2013
    Posts
    241

    Re: formula IF AND OR combination

    Hi,

    Ik looked into your solutions, and Wayne (etaf) got it right, tested and working now,
    Thanks for your assitance ! genius

    Chris

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: formula IF AND OR combination

    you are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Not sure which combination of if formula to use
    By faodavid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-25-2017, 11:33 AM
  2. Replies: 2
    Last Post: 03-13-2015, 01:44 PM
  3. If combination formula
    By Napa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-20-2013, 04:17 AM
  4. Combination formula
    By angelina5678 in forum Excel General
    Replies: 4
    Last Post: 05-12-2010, 12:15 PM
  5. Combination Formula Help
    By cfritz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-02-2006, 08:07 AM
  6. Formula Combination
    By Randy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-05-2006, 08:00 PM
  7. Sum and If combination formula
    By Kim via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-01-2005, 06:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1