# Thread: Need help with some formulas

1. ## Need help with some formulas

Attached is a sheet from a workbook that I use in playing the Pennsylvania Lottery on behalf of a group of 100 people. (This post is duplicated in a Text Box on the sheet.)

The tickets we buy are listed in columns I-N and AE-AJ, in rows 20-99 and 107-186.

When the winning numbers are drawn, I enter them into specific cells, and the worksheet identifies the winning tickets and tells me how much we've won. Cells G3:L3 hold the winning numbers for Tuesday, November 26. Three other groups of white and red cells are set up to hold the winning numbers for the other drawings during the week: AC3:AH3, G11:L11, and AC11:AH:11.

You can enter some dummy winning numbers in Wednesday, Friday, and/or Saturday, and see how the whole thing works. (For example, in Wednesday's drawing, try 3, 27, 39, 44, 46, 10). Overall, this workbook is really useful. It has been developed over several years, often with help from Woody's Lounge/Windows Secrets Lounge members. There's far more to it than just this sheet, but this sheet is the most important one.

The work of identifying winners and reporting on them is done by formulas in hidden cells in Columns R-Z and AN-AV, in rows 20-99 and 107-186.

If I can improve these formulas in one minor way, this worksheet will be perfect. I need some help with improving them. Here's the problem:

Right now, if a red cell in Columns N or AJ is blank and there are no winning numbers entered for that drawing, the formulas wrongly treat that ticket as a winner based on a Powerball or Megaball match. Try it and see. (Example: Remove the 999 in AJ55)

I work around the problem by putting 999 in unused red cells, but that is tacky, given the otherwise sophisticated nature of the worksheet. It is also a pain in the neck.

I need to change the formulas in the hidden cells so that a winner is not reported when there's a blank in a red cell and no numbers entered as winners. If somebody can provide formulas for one set of cells, I can use them to populate the rest of the worksheet.

The solution is probably easy, and I'm sure I could puzzle it out in time, but my skills in this area are weak. Maybe somebody with better skills can help me out.

2. You can test for the count of the values to make sure it has 6 of them:
for eg in AK55:
=IF(COUNTA(AE55:AJ55)<>6,"",ISNUMBER(MATCH(PAT1,PAN1:PAN5,0))+ISNUMBER(MATCH(P AT2,PAN1:PAN5,0))+ISNUMBER(MATCH(PAT3,PAN1:PAN5,0) )+ISNUMBER(MATCH(PAT4,PAN1:PAN5,0))+ISNUMBER(MATCH (PAT5,PAN1:PAN5,0)))

in AL55:
=IF(COUNTA(AE55:AJ55)<>6,"",IF(PAN6=PAT6,"YES",""))

In AM55:
=IF(COUNTA(AE55:AJ55)<>6,"",IF(OR(PABallMatch="YES",PANumberMatches>2),"Winner !",""))

Since the null string is considered larger than 3, you will have to change the conditional formatting as well in AK55 to a formula:
=AND(Q20>3,Q20<>"")

Steve

3. ## The Following User Says Thank You to sdckapr For This Useful Post:

Lou Sander (2013-12-08)

4. Lou,

Try replacing =IF(PAN6=PAT6,"YES","") with =IF(AND(PAN6=PAT6,PAN6<>""),"YES","") in cell AL55 and see if that works

Maud

5. By the way, this is REALLY COOL! Nice Job!!!

6. ## The Following User Says Thank You to Maudibe For This Useful Post:

Lou Sander (2013-12-06)

7. Thanks for recognizing the coolness! This thing has become amazing. We buy dozens of tickets and there are four drawings each week.

It takes me less than five minutes to 1) find the winning numbers for a drawing, 2) enter them into this worksheet, 3) see which tickets won, and for how much money, 4) put that out on Twitter, and 5) update a web site with the results.

You can see the web site by clicking the red and green alligator logo at the upper left of the sheet. You can see the Pennsylvania Lottery site (which reports the winning numbers) by clicking any of the MegaMillions or PowerBall logos.

Over time, I've had most of the ideas about what the workbook should do. I've been able to implement a lot of it myself, but on the harder parts I've relied on The Lounge (and there are LOTS of the "harder parts").

Other sheets in the workbook keep track of the participants, let me email the appropriate ones, keep track of their contributions to the pool and the pool's various bank accounts, the numbers we play, the number of tickets we've bought, etc.

All of this for something that's a lot of fun, but in which there's less than a 1 in a million chance of winning anything serious. Sheesh!

PS - I understand that I am more likely to be eaten by a shark than I am to win one of these jackpots. Double Sheesh!

8. Steve,

I thought I posted this a couple of hours ago. Your first two formulas seem to work fine. When I pasted them into Excel, I got a message that they weren't right, and an offer to correct them. I accepted the correction and all was well. I couldn't see any difference between your formulas and the corrections; maybe the problem was that Excel didn't like the red characters.

The conditional formatting works, but I think it isn't right. Cell Q20 is in a completely different area of the worksheet than the cells whose formatting I'm changing. Please look into it and either tell me I'm wrong or send me a new conditional formatting criterion.

I'm not completely in the dark about this stuff, but I don't want to be the extra cook who spoils the stew that you are sending me. In other words, I don't want to mess with this stuff myself, even though I might be able to figure it out.

9. The problem may be the way that XL2010 does it compared to XL200o used to. It used to be based on the active cell when setting it. The new way is based on the assigned to that format.

Steve

10. I'm using XL2003, if that helps.

11. Steve,

I've populated the whole worksheet with your formulas, and everything seems to work great! (I've learned that this stuff needs to be proven in actual use, though. Errers DO creep in.)

The matter of the Conditional Formatting turns out to be unimportant. When there are no ticket numbers entered into a row, the Number Matches cell remains green. In actual use, this is a handy reminder that the row in question doesn't have any entries. So actually the green cell is a feature, not a bug.

Thanks VERY much for your solution to this little problem. It's something that had been bugging me for a year or more.

12. You are very welcome. I am glad I could help.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•