+ Reply to Thread
Results 1 to 11 of 11

Help with IF)AND) Formula

  1. #1
    Registered User
    Join Date
    12-21-2020
    Location
    Liverpool,England
    MS-Off Ver
    Office 365
    Posts
    10

    Wink Help with IF)AND) Formula

    Hello,

    I need help with a formula that returns either "WIN" or "LOSE" dependent on two calculations.

    The first is =IF(A1>A2,"WIN","LOSE")
    However, the second is =IF(AND(A1<>A2,B1>B2), "WIN","LOSE")

    So I need the figures in column A prioritized unless they are equal, then check the figures in column B to determine the winner.

    Sample attached.

    Thanks in advance!
    Dan
    Attached Files Attached Files
    Last edited by DHoots; 12-21-2020 at 05:10 AM.

  2. #2
    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
    43,893

    Re: Help with IF)AND) Formula

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.
    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

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Help with IF)AND) Formula

    Just guessing:

    =IF(A1=A2,"LOSE",IF(OR(B1>B2,A1>A2),"WIN","LOSE"))
    Quang PT

  4. #4
    Registered User
    Join Date
    12-21-2020
    Location
    Liverpool,England
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Help with IF)AND) Formula

    Thanks for the guess but that doesn't work either :D

  5. #5
    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
    43,893

    Re: Help with IF)AND) Formula

    Draws - are they possible??

  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
    43,893

    Re: Help with IF)AND) Formula

    In D4:
    =IF(B4>B5,"Win",IF(B4<B5,"Lose",IF(AND(B4=B5,C4>C5),"Win",IF(AND(B4=B5,C4=C5),"Draw",IF(AND(B4=B5,C4<C5),"Lose")))))

    in D5:
    IF(D4="Win","Lose",IF(D4="Lose","Win","Draw"))

    Select BOTH cells. Copy paste into D10/D11 and wherever else needed.
    Attached Files Attached Files

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Help with IF)AND) Formula

    Try this in D4 copied down:

    =IF(AND(COUNTIF($B$4:$B$5,B4)=1,MAX($B$4:$B$5)=B4),"WIN",IF(AND(COUNTIF($B$4:$B$5,B4)=2,MAX($C$4:$C$5)=C4),"WIN","LOSE"))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  8. #8
    Registered User
    Join Date
    12-21-2020
    Location
    Liverpool,England
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Help with IF)AND) Formula

    Quote Originally Posted by Glenn Kennedy View Post
    In D4:
    =IF(B4>B5,"Win",IF(B4<B5,"Lose",IF(AND(B4=B5,C4>C5),"Win",IF(AND(B4=B5,C4=C5),"Draw",IF(AND(B4=B5,C4<C5),"Lose")))))

    in D5:
    IF(D4="Win","Lose",IF(D4="Lose","Win","Draw"))

    Select BOTH cells. Copy paste into D10/D11 and wherever else needed.
    Works perfectly thank you! A draw is possible but is highly unlikely!

  9. #9
    Registered User
    Join Date
    12-21-2020
    Location
    Liverpool,England
    MS-Off Ver
    Office 365
    Posts
    10

    Re: Help with IF)AND) Formula

    Quote Originally Posted by AliGW View Post
    Try this in D4 copied down:

    =IF(AND(COUNTIF($B$4:$B$5,B4)=1,MAX($B$4:$B$5)=B4),"WIN",IF(AND(COUNTIF($B$4:$B$5,B4)=2,MAX($C$4:$C$5)=C4),"WIN","LOSE"))
    Also works perfectly! thank you very much!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: Help with IF)AND) Formula

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Help with IF)AND) Formula

    If Kills is weighted more than Damage (i.e, 10^6 times)
    In D4:
    Please Login or Register  to view this content.
    Drag down.

+ 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. Replies: 3
    Last Post: 03-24-2020, 07:27 AM
  2. Replies: 3
    Last Post: 01-10-2019, 09:34 AM
  3. Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  4. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  5. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  6. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  7. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Tags for this Thread

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