Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find data within a spreadsheet | New Users to Excel | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
How do I find the last row containing data in a spreadsheet | Excel Discussion (Misc queries) | |||
Can I sort excel spreadsheet data by fill color of cells? | Excel Discussion (Misc queries) | |||
Pivot table, data from various spreadsheet | Excel Discussion (Misc queries) |