+ Reply to Thread
Results 1 to 8 of 8

Tricky IF OR AND Nested function

  1. #1
    Registered User
    Join Date
    09-01-2015
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Pro Plus 2010
    Posts
    33

    Tricky IF OR AND Nested function

    Good morning all,
    I have a tricky situation I am trying to solve with a nested IF OR AND function.

    In Column A I have names, Columns B and C have input values. In column D I require my function. Example as follows.

    Mark - 1 - 4
    John - 3 - 2
    Mary - 1 - 0
    Susan - 2 - 5
    Andrew - 1 - 1
    Joan - 3 - 3

    My answer in Column D must only use the male names, and calculate an answer based on the difference between B & C.
    If B<C then 0, if B=C then 1, if B>C then 2

    So the output would look like this:
    Mark - 1 - 4 - 0
    John - 3 - 2 - 2
    Mary - 1 - 0 - 0 (or blank)
    Susan - 2 - 5
    Andrew - 1 - 1 - 1
    Joan - 3 - 3

    Also included is if no values are in B, leave D blank.

    I have got this so far in column D, but it is not working too well ...

    IF(B2="","",IF(OR(A2="MARK",A2="JOHN",A2="ANDREW"),AND(B2>C2)*2,IF(B2=C2,1,0)))

    Am I going about this the correct way, or what is it that I am missing?
    Any help is much obliged. My first day here, so I am hoping that this forum is as awesome as it looks!
    Thanks,
    HGL

  2. #2
    Registered User
    Join Date
    09-21-2012
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2003
    Posts
    93

    Re: Tricky IF OR AND Nested function

    =IF(B2="","",IF(B2<C2,0,IF(B2=C2,1,2)))
    drag down till you want.

    hope this helps

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,056

    Re: Tricky IF OR AND Nested function

    With the names of the guys set out in a Table at H1:H3, I'd use this:

    =IF(ISNUMBER(MATCH(A2,$H$1:$H$3,0)),IF(B2<C2,0,IF(B2=C2,1,2)),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,056

    Re: Tricky IF OR AND Nested function

    I missed the blank B condition... use this:

    =IF(B2="","",IF(ISNUMBER(MATCH(A2,$H$1:$H$3,0)),IF(B2<C2,0,IF(B2=C2,1,2)),""))

  5. #5
    Registered User
    Join Date
    09-01-2015
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Pro Plus 2010
    Posts
    33

    Re: Tricky IF OR AND Nested function

    @ Glenn Kennedy.
    Wow, that is brilliant. Works like a charm, thanks you.
    So easy, I couldn't see it. Was trying to be too complicated.
    Much obliged! Thank you.
    HGL

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,056

    Re: Tricky IF OR AND Nested function

    Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

  7. #7
    Registered User
    Join Date
    09-01-2015
    Location
    Port Elizabeth, South Africa
    MS-Off Ver
    Pro Plus 2010
    Posts
    33

    Re: Tricky IF OR AND Nested function

    Thanks Glenn, will do!
    HGL

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,056

    Re: Tricky IF OR AND Nested function

    Cheers.....

+ 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. Tricky INDEX/MATCH Function
    By jj554 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-10-2013, 08:42 PM
  2. Tricky INDEX/MATCH Function
    By jj554 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-10-2013, 07:04 PM
  3. Help with tricky function
    By brodemeyer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2009, 05:16 PM
  4. Tricky function
    By daniello in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2007, 06:28 PM
  5. Tricky Formula - SUMIF function
    By northern_sand in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-27-2006, 03:05 AM
  6. Help With A Tricky SUMPRODUCT Function
    By nevi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-31-2006, 05:55 PM
  7. Look up function-What I need to do is very tricky.
    By swestwood in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-08-2006, 11:35 AM
  8. [SOLVED] Tricky FV function
    By MPuser in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-06-2005, 09:45 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