need help matching two sets of numbers

[MagnumOpus]

Hi all,

I'm working on a lottery spreadsheet and I'm trying to see how often a selected digit matches up with the highlighted digits in another column for accuracy testing purposes. In other words, if a key digit was "1", did it hit for that day or not? As you'll see, there are usually four key digits, so I want to check my accuracy for each one. It looks like this:

G1:P1 = Reference digits 0-9
G2:P2 = Count of digits found in winning number (all non-zero counts are highlighted - I used the countif function here.)
R2:U2 = The 4 key digits for the day

(sorry, I can't seem to post a screen shot)

Anyway, the R2:U2 digits need to compare with the G2:P2 counted digits so that at a glance I can see how accurate my key digits are. I tried the ismatch function but I couldn't get it to work, so maybe it's not the right one.

Thanks for any help.

 

[MagnumOpus]

Really? Lots of views and not one response? Could someone at least tell me how to post a screen shot so that some helpful person can have a visual to see what's going on?

 

[Oaktree]

OK, so:

G1:P1 have the numbers 0-9
R1:U1 have some random numbers (each 0-9) for the day's lottery
G2 =COUNTIF($R$1:$U$1,G1) copied right, telling you how many of each 0-9 number appeared in R1:U1.

I think that's what you've described, but I'm not clear what you're trying to calculate or show next.

 

[MagnumOpus]

it looks sort of like this

winning numbers reference digits key digits for the day

0 1 2 3 4 5 6 7 8 9 1 3 5 9
​

321
452
951
654
118

I have the reference digits in cells g11, and the key digits are in cells r2:u2 (which in this case would be 1,3,5,9 - can't get the formatting right so you can view it correctly). The winning numbers are in cells d2:f2. I'd like to see whether the key digits that I picked for that day actually show up in the winning number that was drawn, and if so how often. To do that, the formula should compare the array of key digits to the array of winning numbers and give me a match count. The reason the reference digits are important is because I want to know which key digits are the strongest to play going forward, but from a historical basis - ie, if 3 shows up as a key digit, is it stronger to play than say a 5 because 5s hit more often in the winning number? Maybe that's two different functions or formulas I don't know. I just don't know which function to use, or how to tell it to do any of that.

 

[MagnumOpus]

Ok, I tried that formula but it's giving me the same results as what I had. But I did figure out how to post a picture of what i have so far. Basically, I want to compare key digits to winning numbers in an entirely different column and calculate in percentage how many of the winning digits were trapped by my array of key digits. So for 6/21 midday the percentage of winning digits trapped would be 33%, but for 6/21 evening it would be 0%. But on 6/18 evening it would be 66% because two winning digits were trapped. The end result should be that depending on historical results, I can determine how seriously I should take any particular key digit that appears. This screen shot captures from a1:u9 and it looks like this:

<table width="648" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:2503;width:53pt" width="70"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4124;width:87pt" width="116"> <col style="mso-width-source:userset;mso-width-alt:512; width:11pt" width="14" span="3"> <col style="mso-width-source:userset;mso-width-alt:512; width:11pt" width="14" span="10"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1365; width:29pt" width="38" span="4"> </colgroup><tbody><tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt;width:53pt" width="70" height="19">
</td> <td class="xl76" style="width:48pt" width="64">
</td> <td class="xl77" style="width:87pt" width="116">
</td> <td class="xl77" style="width:11pt" width="14">
</td> <td class="xl77" style="width:11pt" width="14">
</td> <td class="xl77" style="width:11pt" width="14">
</td> <td class="xl79" style="width:11pt" width="14">0</td> <td class="xl79" style="border-left:none;width:11pt" width="14">1</td> <td class="xl79" style="border-left:none;width:11pt" width="14">2</td> <td class="xl79" style="border-left:none;width:11pt" width="14">3</td> <td class="xl79" style="border-left:none;width:11pt" width="14">4</td> <td class="xl79" style="border-left:none;width:11pt" width="14">5</td> <td class="xl79" style="border-left:none;width:11pt" width="14">6</td> <td class="xl79" style="border-left:none;width:11pt" width="14">7</td> <td class="xl79" style="border-left:none;width:11pt" width="14">8</td> <td class="xl79" style="border-left:none;width:11pt" width="14">9</td> <td class="xl71" style="width:48pt" width="64">
</td> <td colspan="4" class="xl79" style="width:116pt" width="152">Key Digits For The Day</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl72" style="height:14.4pt" height="19">6/21/2011</td> <td class="xl70">Midday</td> <td class="xl75">313</td> <td class="xl78">3</td> <td class="xl78">1</td> <td class="xl78">3</td> <td class="xl80" style="border-top:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">2</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl71">
</td> <td class="xl71">1</td> <td class="xl71">5</td> <td class="xl71">6</td> <td class="xl71">7</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl71" style="height:14.4pt" height="19">
</td> <td class="xl70">Evening</td> <td class="xl75">385</td> <td class="xl78">3</td> <td class="xl78">8</td> <td class="xl78">5</td> <td class="xl80" style="border-top:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl71">
</td> <td class="xl71">1</td> <td class="xl71">5</td> <td class="xl71">6</td> <td class="xl71">7</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl72" style="height:14.4pt" height="19">6/20/2011</td> <td class="xl70">Midday</td> <td class="xl75">497</td> <td class="xl78">4</td> <td class="xl78">9</td> <td class="xl78">7</td> <td class="xl80" style="border-top:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl71">
</td> <td class="xl71">1</td> <td class="xl71">5</td> <td class="xl71">6</td> <td class="xl71">7</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl71" style="height:14.4pt" height="19">
</td> <td class="xl70">Evening</td> <td class="xl75">923</td> <td class="xl78">9</td> <td class="xl78">2</td> <td class="xl78">3</td> <td class="xl80" style="border-top:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl71">
</td> <td class="xl71">1</td> <td class="xl71">5</td> <td class="xl71">6</td> <td class="xl71">7</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl74" style="height:14.4pt" height="19">6/19/2011</td> <td class="xl70">Midday</td> <td class="xl75">023</td> <td class="xl78">0</td> <td class="xl78">2</td> <td class="xl78">3</td> <td class="xl80" style="border-top:none;font-size:11.0pt; color:#006100;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl71">
</td> <td class="xl71">1</td> <td class="xl71">5</td> <td class="xl71">6</td> <td class="xl71">7</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl73" style="height:14.4pt" height="19">
</td> <td class="xl70">Evening</td> <td class="xl75">920</td> <td class="xl78">9</td> <td class="xl78">2</td> <td class="xl78">0</td> <td class="xl80" style="border-top:none;font-size:11.0pt; color:#006100;font-weight:400;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border:.5pt solid windowtext; background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl71">
</td> <td class="xl71">1</td> <td class="xl71">5</td> <td class="xl71">6</td> <td class="xl71">7</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl72" style="height:14.4pt" height="19">6/18/2011</td> <td class="xl70">Midday</td> <td class="xl75">343</td> <td class="xl78">3</td> <td class="xl78">4</td> <td class="xl78">3</td> <td class="xl80" style="border-top:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">2</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl71">
</td> <td class="xl71">0</td> <td class="xl71">4</td> <td class="xl71">5</td> <td class="xl71">7</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl71" style="height:14.4pt" height="19">
</td> <td class="xl70">Evening</td> <td class="xl75">354</td> <td class="xl78">3</td> <td class="xl78">5</td> <td class="xl78">4</td> <td class="xl80" style="border-top:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none; font-size:11.0pt;color:#006100;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border:.5pt solid windowtext;background:#C6EFCE;mso-pattern:black none" align="right">1</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl80" style="border-top:none;border-left:none" align="right">0</td> <td class="xl71">
</td> <td class="xl71">0</td> <td class="xl71">4</td> <td class="xl71">5</td> <td class="xl71">7</td> </tr> </tbody></table>

 

[Oaktree]

Try:

=SUMPRODUCT(--(ISNUMBER(MATCH($G$1:$P$1,R2:U2,0))),G2:P2)/3 copied down

 

[MagnumOpus]

BINGO! That did the trick. Thanks for your help!