1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Need help with excel formula

Discussion in 'Business Applications' started by SpyKiller112, Nov 1, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. SpyKiller112

    SpyKiller112 Guest Thread Starter

    Joined:
    Aug 23, 2004
    Messages:
    521
    Is there a way I can make a formula in Excel so that it will only count certain combo's of numbers that up to certain numbers?

    for instance... im working on a project for a statistics class and I have to make a game with payouts and such.

    I have generated two columns of random numbers 1-6
    It looks like this: (the numbers represent colors, 1=red, 2=orange etc)

    Color 1 Color 2 Total Win/Lose
    2 6 8 Win
    4 4 8 Win
    5 2 7 Lose
    6 3 9 Lose

    I have the formula for the Win/Lose column set up so that if the number in the total column is 4,6, or 8 it will say "win" but, i only wanted to count 1+3, and 3+1 for the number 4, but as we all know 2+2=4 aswell and I cant have that.

    So I need help making my excel sheet only recognizing the follwing as a "Win":
    1+3 =4
    3+1 =4
    1+5 =6
    5+1 =6
    3+5 =8
    5+3 =8

    I attached a sample of my work.
     

    Attached Files:

  2. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    62,990
    First Name:
    Chuck
    Since the number of winning combinations is fairly small (only six), here a quick way I did it.

    The "Sum" column is equal to the second number plus ten times the first number. This creates a unique set of sums that do not repeat for all possible number combinaions

    Use the OR function to see if the sum is equal to any of the six winning sums

    Use the IF function to check the result of the OR function. If true, then return the text "Win". If false, then return "No Win" (or " " if you wish to change it).

    I created a list of all available combinations just to test the formula. I left it in the file and you can delete it.
     

    Attached Files:

  3. SpyKiller112

    SpyKiller112 Guest Thread Starter

    Joined:
    Aug 23, 2004
    Messages:
    521
    ok thanks, i used that and a bit of my own knowledge to create something that worked!
     
  4. silver_rs

    silver_rs

    Joined:
    Nov 6, 2006
    Messages:
    2
    I would have just used an "and" funtion:

    if(and(c2=4),(a2<>2)),"Win",if((c2=6),"Win",if((c2=8),"Win"))
     
  5. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    62,990
    First Name:
    Chuck
    Hi silver_rs, and welcome to TSG.

    I got a syntax error when I tried using your formula in cell D2 and using MS-Excel 2003.

    Is this what you meant:

    Code:
    =IF(AND[B][COLOR="Red"]([/COLOR][/B](C18=4),(A182)[B][COLOR="Red"])[/COLOR][/B],"Win",IF((C18=6),"Win",IF((C18=8),"Win"))[B][COLOR="Red"])[/COLOR][/B]
    If so, you get extra "Wins" for 4+2=6 and 3+3=6 when SpyKiller112 only wanted 1+5=6 to win when the total is 6. You also get extra "Wins" when C2 equals 8 but is not made by adding only 3 and 5.

     
  6. silver_rs

    silver_rs

    Joined:
    Nov 6, 2006
    Messages:
    2
    Sorry, got in a hurry when answering the question and didn't read the entire criteria. You can still use the AND and the OR in combination to get the results you need:

    =IF(AND((C2=4),OR((A2=1),(A2=3))),"Win",IF(AND((C2=6),OR((A2=1),(A2=5))),"Win",IF(AND((C2=8),OR((A2=3),(A2=5))),"Win"))).

    This is basically saying that if we get a 4 and cell A2 is a 1 or a 3 then "Win" and similar for total of 6 and 8.
     
  7. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    62,990
    First Name:
    Chuck
    Yes, that works much better. (y)

    As long as we're combining AND's and OR's we can eliminate the need for adding the two numbers and just check for winning combinations of 1, 3 and 5 directly...

    Code:
    =IF(AND(A2=1,OR(B2=3,B2=5)),"Win",IF(AND(A2=3,OR(B2=1,B2=5)),"Win",IF(AND(A2=5,OR(B2=1,B2=3)),"Win"," ")))
     
  8. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Posting one of my fave methods (for that reason only & no other):

    =IF(ISNA(MATCH(A1&B1,{"13";"15";"31";"35";"51";"53"},0)),"No win","Win")
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/514774

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice