Lotto Trigger

[Steve 1962]

Hi

I am attempting to have winning lotto combination flag a "winner" notification in cell C17. Currently it operates OK for the lottery however, an extra winning combination has been introduced which renders my formula useless. Previously, a minimum of three "main numbers" (column "A") and a minimum of one "supplementary" (column "B") would trigger a "winner" in cell C17. Now, an additional two prizes have been introduced which trigger a "winner" but I can't get the formula to work. The new two combinations are in rows 13 & 14 (eg: 2 main + 2 supplementary or 1 main + 2 supplementary). Any suggestions as to how I can modify my current formula to accommodate the change.

Thanks

Steve

Excel Workbook
	A	B	C
1	Main Number	Supplementary
2	6	2
3	6	1
4	6	0
5	5	2
6	5	1
7	5	0
8	4	2
9	4	1
10	4	0
11	3	2
12	3	1
13	2	2
14	1	2
15
16
17	3	1	Winner
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C17	=IF(OR(A17>3,AND(A17>2,B17>0)),"Winner","")

 

[T. Valko]

Hi

I am attempting to have winning lotto combination flag a "winner" notification in cell C17. Currently it operates OK for the lottery however, an extra winning combination has been introduced which renders my formula useless. Previously, a minimum of three "main numbers" (column "A") and a minimum of one "supplementary" (column "B") would trigger a "winner" in cell C17. Now, an additional two prizes have been introduced which trigger a "winner" but I can't get the formula to work. The new two combinations are in rows 13 & 14 (eg: 2 main + 2 supplementary or 1 main + 2 supplementary). Any suggestions as to how I can modify my current formula to accommodate the change.

Thanks

Steve

Excel Workbook
	A	B	C
1	Main Number	Supplementary
2	6	2
3	6	1
4	6	0
5	5	2
6	5	1
7	5	0
8	4	2
9	4	1
10	4	0
11	3	2
12	3	1
13	2	2
14	1	2
15
16
17	3	1	Winner
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C17	=IF(OR(A17>3,AND(A17>2,B17>0)),"Winner","")

Maybe this...

=IF(OR(AND(A17>=3,B17>=1),AND(A17>=2,B17>=2),AND(A17>=1,B17>=2)),"Winner","")

 

[Steve 1962]

Thanks Biff

Seems to be OK except that when 6 main numbers are drawn with 0 supplementaries, the "winner" indicator goes blank.

Steve

 

[T. Valko]

Excel Workbook
	A	B	C
1	Main Number	Supplementary
2	6	2
3	6	1
4	6	0
5	5	2
6	5	1
7	5	0
8	4	2
9	4	1
10	4	0
11	3	2
12	3	1
13	2	2
14	1	2
15
16
17	3	1	Winner
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C17	=IF(OR(A17>3,AND(A17>2,B17>0)),"Winner","")

Thanks Biff

Seems to be OK except that when 6 main numbers are drawn with 0 supplementaries, the "winner" indicator goes blank.

Steve

Are you saying that the numbers in the range A2:B14 represent all the winning combinations?

 

[Steve 1962]

Yes - that's correct.

 

[T. Valko]

Yes - that's correct.

OK, try this...

=IF(COUNT(A17:B17)=2,IF(SUMPRODUCT(--(A2:A14=A17),--(B2:B14=B17)),"Winner",""),"")

 

[Steve 1962]

Just ran all the different winning combinations and I am getting a "0" as the output for all combinations, even 1 main number + 0 supplementary which is not a winning combination. I'll check the formula again and see if I've made a mistake.

 

[Steve 1962]

Also, A17 & B17 are calculated cells. Not sure if this makes a difference or not.

 

[T. Valko]

Just ran all the different winning combinations and I am getting a "0" as the output for all combinations, even 1 main number + 0 supplementary which is not a winning combination. I'll check the formula again and see if I've made a mistake.

Here's a small sample file that demonstrates this.

zzzSteve1962.xls 15kb

http://cjoint.com/?AGEqt72t3ue

 

[Steve 1962]

Thanks Biff for the file that you sent through. Yes, it does appear to be working OK. Unfortunately I'm not feeling too well at the moment and it's rather late here. I'll need to go to bed now but I'll return to this first thing in the morning as I do want to fix this (it's been bugging me for a while now).

Thanks for your help. I will update this post in the morning. Obviously I'm doing something wrong on my end.

Much appreciated and I'll let you know how I go.

Steve