# Need help with excel formula

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

Not open for further replies.

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:

• ###### tech.xls
File size:
13.5 KB
Views:
75

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:

• ###### Win_NoWin.xls
File size:
17.5 KB
Views:
123

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

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"))

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

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.

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

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

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")

As Seen On