Lotto Formula

[Shoota]

My name is Maurice, Can you help me please. I am in New Zealand and I have these numbers on the below that I take each week in our country lottery. I have a conditional formula that highlights the winning numbers by colour from results in A1 to F1 for the first 6 numbers then bonus number in G1 and then powerball number in H1. On the right hand side is the results list for the prizes. Could you possibly have a formula that would show starting in I1 through to I7 that there is a prize to be collected from the shop from column A2-A7 through J2-J7. I have the first 6 numbers as Yellow fill with Dark yellow text, Bonus is Green with Dark Green text, Power Ball is Red with Dark Red text. I would like it if it could show Lotto Division 1,7 or Powerball Division 1,7 if the numbers do come up. I got the bottom formulas working ok for Lotto but the powerball come up with prize power 5 as soon as I put the number 2 in A1. I would like no result from power until row 11 is counted. I don't understand coding, but some formula. Hoping someone may be able to help. Thank You, Maurice.




[TABLE="width: 1007"]
<colgroup><col><col span="10"><col><col><col><col></colgroup><tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]E1[/TD]
[TD]F1[/TD]
[TD]G1[/TD]
[TD]H1[/TD]
[TD]I1[/TD]
[TD]J1[/TD]
[TD]row 1
[/TD]
[TD]Col L[/TD]
[TD]Col M[/TD]
[TD]Col N[/TD]
[TD]Col O
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]7[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]1[/TD]
[TD]27[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]row 2[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lotto 1[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]21[/TD]
[TD]12[/TD]
[TD]17[/TD]
[TD]6[/TD]
[TD]29[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]13[/TD]
[TD]row 3[/TD]
[TD]5[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Lotto 2[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]21[/TD]
[TD]12[/TD]
[TD]32[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]13[/TD]
[TD]18[/TD]
[TD]row 4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lotto 3[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]33[/TD]
[TD]30[/TD]
[TD]25[/TD]
[TD]18[/TD]
[TD]34[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]17[/TD]
[TD]23[/TD]
[TD]row 5[/TD]
[TD]4[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Lotto 4[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]36[/TD]
[TD]33[/TD]
[TD]31[/TD]
[TD]28[/TD]
[TD]37[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]21[/TD]
[TD]28[/TD]
[TD]row 6[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lotto 5[/TD]
[/TR]
[TR]
[TD]38[/TD]
[TD]40[/TD]
[TD]38[/TD]
[TD]35[/TD]
[TD]29[/TD]
[TD]39[/TD]
[TD]34[/TD]
[TD]35[/TD]
[TD]27[/TD]
[TD]33[/TD]
[TD]row 7[/TD]
[TD]3[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Lotto 6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]row 8[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Lotto 7[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]row 9[/TD]
[TD][/TD]
[TD][/TD]
[TD]Power Ball[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]row 10[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Power 1[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]row 11[/TD]
[TD]5[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Power 2[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]row 12[/TD]
[TD]5[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Power 3[/TD]
[/TR]
[TR]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]FALSE[/TD]
[TD]row 13[/TD]
[TD]4[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Power 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 14[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Power 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 15[/TD]
[TD]3[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD]Power 6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row 16[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Power 7[/TD]
[/TR]
</tbody>[/TABLE]



Lotto row 12
=IF(A9=6,$O$2,IF(A9+A10=6,$O$3,IF(A9=5,$O$4,IF(A9+E10=5,$O$5,IF(A9=4,$O$6,IF(A9+A10=4,$O$7,IF(A9=3,$O$8)))))))

Powerball row 13 =IF($A9+A11=7,$O$10,IF(A9+A10+A11=7,$O$11,IF(A9+A11=6,$O$12,IF(A9+A10+A11=6,$O$13,IF(A9+A11=5,$O$14,IF(A9+A10+A11=5,$O$15,IF(A9+A11=4,$O$16 )))))))

 

[sergioMabres]

Hi Maurice,
You could use

[COLOR=#444444][FONT=Calibri]=IF(NOT([/FONT][/COLOR][COLOR=#5F8CED][FONT=Calibri]A12[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]),"",IF([/FONT][/COLOR][COLOR=#EB5E60][FONT=Calibri]$A9[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]+[/FONT][/COLOR][COLOR=#8D61C2][FONT=Calibri]A11[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]=7,[/FONT][/COLOR][COLOR=#2D9639][FONT=Calibri]$O$10[/FONT][/COLOR][COLOR=#444444][FONT=Calibri],IF([/FONT][/COLOR][COLOR=#EB5E60][FONT=Calibri]A9[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]+[/FONT][/COLOR][COLOR=#BF4C91][FONT=Calibri]A10[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]+[/FONT][/COLOR][COLOR=#8D61C2][FONT=Calibri]A11[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]=7,[/FONT][/COLOR][COLOR=#E38222][FONT=Calibri]$O$11[/FONT][/COLOR][COLOR=#444444][FONT=Calibri],IF([/FONT][/COLOR][COLOR=#EB5E60][FONT=Calibri]A9[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]+[/FONT][/COLOR][COLOR=#8D61C2][FONT=Calibri]A11[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]=6,[/FONT][/COLOR][COLOR=#377F9E][FONT=Calibri]$O$12[/FONT][/COLOR][COLOR=#444444][FONT=Calibri],IF([/FONT][/COLOR][COLOR=#EB5E60][FONT=Calibri]A9[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]+[/FONT][/COLOR][COLOR=#BF4C91][FONT=Calibri]A10[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]+[/FONT][/COLOR][COLOR=#8D61C2][FONT=Calibri]A11[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]=6,[/FONT][/COLOR][COLOR=#5F8CED][FONT=Calibri]$O$13[/FONT][/COLOR][COLOR=#444444][FONT=Calibri],IF([/FONT][/COLOR][COLOR=#EB5E60][FONT=Calibri]A9[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]+[/FONT][/COLOR][COLOR=#8D61C2][FONT=Calibri]A11[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]=5,[/FONT][/COLOR][COLOR=#EB5E60][FONT=Calibri]$O$14[/FONT][/COLOR][COLOR=#444444][FONT=Calibri],IF([/FONT][/COLOR][COLOR=#EB5E60][FONT=Calibri]A9[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]+[/FONT][/COLOR][COLOR=#BF4C91][FONT=Calibri]A10[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]+[/FONT][/COLOR][COLOR=#8D61C2][FONT=Calibri]A11[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]=5,[/FONT][/COLOR][COLOR=#8D61C2][FONT=Calibri]$O$15[/FONT][/COLOR][COLOR=#444444][FONT=Calibri],IF([/FONT][/COLOR][COLOR=#EB5E60][FONT=Calibri]A9[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]+[/FONT][/COLOR][COLOR=#8D61C2][FONT=Calibri]A11[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]=4,[/FONT][/COLOR][COLOR=#2D9639][FONT=Calibri]$O$16[/FONT][/COLOR][COLOR=#444444][FONT=Calibri]))))))))[/FONT][/COLOR]

For Powerball row 13
Cheers
Sergio