Lottery Spreadsheet - Find data in a table and color it...
 

Howdy,

When Powerball gets up to the $200 million mark, we start pooling up money
at work and buying a bunch of tickets. I want to make a spreadsheet where I
can type in all the numbers we buy and then enter the numbers drawn and have
it calculate the winnings for us.

I figured it'd be pretty simple but don't know how to get started.

If I type in the winning numbers into 6 cells, I could then query the table
data looking for a match to those numbers in the sample data. Could I then
turn the background to red or something to flag them all?

After that can I do some sort of other lookup that would go row by row and
see how many red cells there are and count them somehow?

Any help and advice would be great.

Lottery Spreadsheet - Find data in a table and color it...
 

Mr. B,

If your numbers are entered in columns A to F, with the PowerBall numbers in F, enter your numbers
starting in Row 4, down the sheet, and the winning numbers in Row 2:

Then in G4, array enter (enter using Ctrl-Shift-Enter):
=SUM(COUNTIF(A4:E4,$A$2:$E$2))

In H4, enter
=F4=$F$2

In I4, enter

=IF(H4, CHOOSE(G4 +1,"Just the Ball!","Ball + 1","Ball + 2","Ball + 3","Ball + 4","We're rich!!!!
Yippeee!"), CHOOSE(G4 +1,"Nuthin!!!","1 number","2 numbers","3 numbers","4 numbers","We're
semi-rich!"))

Then copy G4:I4 down to match your list of numbers.


After you enter the winning numbers, sort rows 4 to whatever based first on column H descending, and
second on column G descending.

When you win, I expect a very small cut ;-)

HTH,
Bernie
MS Excel MVP


"Mr B" wrote in message
...
Howdy,

When Powerball gets up to the $200 million mark, we start pooling up money
at work and buying a bunch of tickets. I want to make a spreadsheet where I
can type in all the numbers we buy and then enter the numbers drawn and have
it calculate the winnings for us.

I figured it'd be pretty simple but don't know how to get started.

If I type in the winning numbers into 6 cells, I could then query the table
data looking for a match to those numbers in the sample data. Could I then
turn the background to red or something to flag them all?

After that can I do some sort of other lookup that would go row by row and
see how many red cells there are and count them somehow?

Any help and advice would be great.