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

[Mr B]

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.

 

[Bernie Deitrick]

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