Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Lottery results checker

0

Hello. I created a sheet to check the lottery results for a works syndicate. This worked perfectly. I now need to check another lottery type and I thought I could adapt the previous sheet. Previously I checked six numbers with 1 bonus number. Now I am working on five main numbers with six bonus numbers. That is to say, one bonus number spread over six payout options. The sheet works perfectly in results 1 to 5 plus bonus ball but I get multiple errors on staggered results. For instance, three balls plus bonus gives the result 4, the same as 4 main balls on their own.

In the sample file I win the top prize but the bottom prize is an error. In the line with the pink marker because only the one bonus number counts. The other one should be counted as 1 plus bonus.

I think the process is fairly easy to understand by playing around with cells E5:J5. In the final sheet they will be the only selectable cells as the rest will all be processed in the background. There are 49 main balls and 14 thunderballs (bonus balls) to choose from.

Thank you.

Answer
Discuss

Answers

0
Selected Answer

Hi again MrEMann

Not sure I understand your sheet but I assume (for the non-Lucky Dips):

  1. cells E16:J20 represent the numerical choices (5 main + one thunderball) of the 5 participants
  2. cells X11:AC14 are meant to mark how those choices compare with the balls drawn
  3. cells E11:15 relect the latter but use conditional formatting to give a graphical representation to show matches from E16:J20 (in black)

If so, I changed your formula in X11 from:

=COUNTIF(E16,E$5)

to: 

=COUNTIF($E16:$J16,E$5)

so that the drawn number in E5 is "looked for" in the row E16:J16 (since it could be the second numver or higher in the person's choice). That formula is copied across and down pink-filled range X11:AB14.

The thunderball formula (column AC) is left as is, e.g. AC11 is still: 

=COUNTIF(J16,J$5)
since thunderball result J5 must be in column J only (right?) but I chnaged the Totals in AD to count ONLY the main ball matches ("Mains" in orange cells) e.g. AD11 is:
=SUM(X11:AB11)

This means that in column O, we can use a COUNTIFS formula and just change the main ball and thunderball matches e.g. in O11 it's:

=COUNTIFS($AD$11:$AD$15,5,$AC$11:$AC$15,1)

to look for 5 main balls and a thunderball whereas O12 mathces 5 main but no thunderball match using:

=COUNTIFS($AD$11:$AD$15,5,$AC$11:$AC$15,0)

and so on for the various prize combinations.

It looks like the scoring is right for the "main draw" (but you'd need to expand this logic for the Lucky Dips sections).

Hope this helps 

Discuss

Discussion

Thank you John. Working perfectly, as always with your excellent guidance.
MrEMann (rep: 22) Oct 19, '24 at 6:50 am
Glad that was right. Thanks fir selecting my Answer, MrEMann. 
John_Ru (rep: 6437) Oct 19, '24 at 7:47 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login