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