Need Help with a Lottery Worksheet

Topic

New Reply

This has to do with the Powerball and Mega Millions lottery drawings. I run a quarterly nonprofit lottery pool that relies on the attached worksheet. I also have a neurological condition that interferes with my ability to solve complex problems. I REALLY need help with this one.

THE SITUATION:  In the past, Mega Millions drawings were held every Tuesday and Friday, and Powerball drawings were held every Wednesday and Saturday. The accompanying worksheet could be used to determine the lottery name (MM or PB), days and dates for the next 52 drawings. (52 is the number of drawings in a 13-week quarter.)

Enter any TUE, WED, FRI or SAT date in the top right cell, and the rest of the grid will automatically be populated with the information for the next 51 drawings.

Enter any SUN, MON, or THU date, and you will see a worksheet full of errors. This is not a big problem, since I can easily re-enter a date.

THE PROBLEM: Powerball has added a Monday drawing, and my worksheet can’t handle it. The worksheet has become useless.

THE SOLUTION: Rewrite the worksheet to accommodate Monday drawings. Extend it to 65 lines, to accommodate 13 weeks of 15 drawings per week.

I’ve fooled around with this, but couldn’t get it to work.

It would be nice, but surely not necessary, to respond to a “bad” date entry with something other than a mess.

 

Lou Sander

Reply | Quote

Replies

I submitted this without signing in, so couldn’t attach a file. I signed in and resubmitted it.

Lou Sander

Reply | Quote

The long-winded formulae in this simple spreadsheet looks to me like using a sledgehammer to crack a nut. It also makes life difficult when something changes.

Why not just manually enter the first 5 rows to get the first week and then have the rows after that referencing the row 5 above it ? e.g. if you’ve put “Mon” in C3 then C8 will be =C3 etc etc

Just so much simpler than what you have at the moment.

Windows 10 Home 22H2, Acer Aspire TC-1660 desktop + LibreOffice, non-techie

Reply | Quote

That might work, but what I gave you is part of a much bigger situation, with conditional formatting, automation, etc. Nevertheless, I’ll give it a try.

Lou Sander

Reply | Quote

Hi Lou

You can easily fix this by using different formulas.

In my attached file, I just changed the formula in cell [B3] to

[B3]=CHOOSE(WEEKDAY(D3),””,”PB”,”MM”,”PB”,””,”MM”,”PB”)

..and copied it down to cell [B67]

And then I used this formula in cell [D4]

[D4] =IF(OR(C3=”Wed”,C3=”Sat”),D3+2,D3+1)

..and copied that formula to range [D4;D8]

seems to give you what you want

Note: If you enter a Thursday or Sunday date in cell [D3] (e.g. 11-25-21 or 11-28-21), you’ll just get a blank for the Game type in cell [B3], but everything following will be OK

Drawing-Dates-zeddy-1

zeddy

1 user thanked author for this post.

ussrankin

Reply | Quote

Hi Lou

If you have any questions regarding the file I posted dealing with this, please ask.

zeddy

Reply | Quote

Zeddy,

Thanks for the response to my inquiry. Due to extreme busy-ness, I just haven’t had time to look at it.

I’ll try to get to it today.

Regards,

Louis “Skip” Sander

The USS Rankin Association

Lou Sander

Reply | Quote

PERFECT! 

Great work, and thanks from the bottom of my heart. I couldn’t have done this without your help.

BTW, the addition of Monday drawings came with a restriction on how many lottery tickets one can buy at one time, and when one can buy them. I will work on the specifics of that, and post the problem here. Please keep an eye out for it. (I’m able to state problems pretty clearly, but I’m nearly useless at coming up with solutions – too much concentrated thinking.)

 

Lou Sander

Reply | Quote

OOPS! NOT perfect!

The dates break down after the first week or so.

Enter 11/30/21 in the yellow cell. All is well through Tue 12/7/21. But the next entry down should be Wed 12/8/21.  But instead it is is Fri 12/10/21. No Wed.

 

Lou Sander

Reply | Quote

Lou,

The issue you described occurs as the formula didn’t get copied all the way down.

Cell D8 =IF(OR(C7=”Wed”,C7=”Sat”),D7+2,D7+1)
Cell D9 =D5+7 … and so on down the list.

Easy fix is to copy/fill cell D8 down to the end of the list.

I’ve done that for you in the attached file (Drawing-Dates-zeddy-2.xlsx).

Hopefully that’s got it sorted for you.

Regards

1 user thanked author for this post.

zeddy

Reply | Quote

Hi btbs

Welcome to the Lounge!

And let me be the first to give you a big THANKS for fixing my boo boo.

Ooops! When you think you have double-clicked the cell to copy formulas all the way down maybe I should’ve checked before posting! But that’s the great thing about this Lounge – we have plenty of eyes to check!

And Lou, we look forward to seeing your additional requirements. And let’s not forget, you have helped others here too!!

zeddy

 

 

1 user thanked author for this post.

ussrankin

Reply | Quote

After a reasonably thorough check, your solution NOW seems to be PERFECT.

Thanks again for the help.

My lottery workbook is extensive and pretty complex. I’ve had a LOT of help with it over the years, most recently from Retired Geek and Maudibe. It started out in 2007, and has grown since then. Right now, it takes me less than five minutes to post the results of a drawing to THIS website. If we ever win anything big (huge odds against it), I’m going to become a big-time benefactor of the Lounge.

Lou Sander

Reply | Quote

Well, there’s more…

Each of the drawings (MM or PB) has a minimum winnings number associated with it. That number doesn’t change over the life of a pool, but it could change from pool to pool. Right now it is $2.00 for MM and $4.00 for PB. These are stored in cells on a different tab. The cells are named MinWinningsMM and MinWinningsPB

To the right of the Date column (the one containing the yellow cell at the top), I need to put a “Minimum Winnings” column.

I can’t figure out how to do it, since the MM and PB are right now the results of a formula, rather than the text values “MM” or “PB”.

Lou Sander

Reply | Quote

Lou,

Is the attached what you are after?

If the min winnings change on/from any date go to that date and change the value in the formula in column E. If it is going to be that value from then on, copy the updated formula down to the bottom.

Regards

Reply | Quote

This works great as it is, but one more refinement would be desirable. (Sorry that I didn’t cover all this from the beginning. I thought it would be best to go step-by-step on such a complex challenge.)

I want to use Conditional Formatting to make all cells in an MM line yellow, and all those in a PB line orange.

I can do that on my own by differentiating the two by the 2 or 4 minimum winnings. But if the Lottery Gods ever see fit to make the minimum winnings the same for both drawings, I am back to square one.

Is there some way to make “MM” or “PB” appear in the Game column, rather than the CHOOSE function that produces them? I think that would be the final piece to the puzzle. If that can’t be done, I’ll worry about the Lottery Gods later.

BTW, what’s the purpose of the number entered into cell H3?

Lou Sander

Reply | Quote

Lou,

The attached has the conditional formatting colours that you’ve asked for. If you want to change the colours select column B then conditional formatting then Manage Rules and then you’ll be able to click on each condition and “Edit” as required.

As far as the number in H3 is concerned I’m guessing Zeddy was using it for testing purposes. I’ve removed it in the attached.

I’m not quite sure what you mean by “make “MM” or “PB” appear in the Game column, rather than the CHOOSE function that produces them”. On my screen I only see MM or PB and only see the formula if I click on the cell. If what you are after is to literally have only MM or PB and nothing else you’d need to use a macro/user defined function. This is easy to do but would change the spreadsheet from xlsx to xlsm and may cause problems on some systems which have macros disabled.

Regards

 

1 user thanked author for this post.

ussrankin

Reply | Quote

You’ve got it! Thanks. I’d rather hear directly from zeddy about H3.

Lou Sander

Reply | Quote

I’ve been out of commission for a while. Sorry.

The attached file is the best to date. Note the credits to zeddy and btbs. Also note that any drawings already held are grayed out. To see this, enter 12/10/21 as the first date.

More work is needed, but I think it might be easy. (Remember that I have lost a lot of problem-solving ability due to a neurological condition.)

The major need is to list the number of lottery drawings “to go” before the end of the current pool. I need a count for each game, and the total count for both games. By convention, the current day’s drawing is counted as one “to go”.

Each of these tables covers one calendar quarter, beginning with the first drawing in JAN, APR, JUL, and OCT, and ending with the last drawing in MAR, JUN, SEP, and DEC. The number of drawings in each pool are listed in cells E7, F7 & G7 on the Parameters tab. It’s probably good to use them, since they change over time.

A very minor need is to eliminate the “mess” in some columns when a THU or SUN is entered as the first date. That will never be done on purpose, so it doesn’t really matter. But if it’s a challenge, feel free to take it on.

Lou Sander

Reply | Quote

Oops! File didn’t upload. It was .xlsm, which is forbidden

I changed it to .xlsx

Lou Sander

Reply | Quote

Lou,

The ‘drawings to go’ on the ‘Finances’ sheet have been added.

When a THU or SUN is entered… The ERR in columns E & F is deliberate and put there by Zeddy to show that a wrong day has been entered. As what would normally be a number cell has text the subsequent cells in column F will go to #VALUE! as a warning that a number is trying to be added to text (ERR). The options are (1) leave it as-is as a very obvious reminder that the wrong day has been entered to make them; (2) Put ERR instead of #VALUE!; (3) leave them blank; (4) something else, if so, what? Which do you want to do.

Re …

ussrankin wrote:

Each of these tables covers one calendar quarter, beginning with the first drawing in JAN, APR, JUL, and OCT, and ending with the last drawing in MAR, JUN, SEP, and DEC. The number of drawings in each pool are listed in cells E7, F7 & G7 on the Parameters tab. It’s probably good to use them, since they change over time.

I’m not sure what you want done with this.

I’m also not sure what you want done, if anything, with the parameters page in general.

Regards

PS You should be able to upload .xlsm so not sure what went wrong with your earlier attempt.

1 user thanked author for this post.

ussrankin

Reply | Quote

Thanks for the fix!

I’ll just leave the ERR stuff as it is.

The “calendar quarter” stuff is a product of my own fuzzy thinking. Just ignore it.

The Parameters page is extensively used on the many other worksheets in this workbook. The workbook has been around, and constantly under development, for 10+ years. It needs to be cleaned up, but so far it serves its purpose. After each drawing (5 per week) I can update THIS website in under five minutes.

Not bad for an old guy whose brain is turning to Swiss cheese!

Lou Sander

Reply | Quote

Hi Lou

As an old guy you are always impressive, and your ship’s motto “Ready Now” is very appropriate for all you do. Keep doing your stuff!

zeddy

Reply | Quote