excel match feature

[simon31]

Hello there,

I use countif formula to find out how many times in one year 8 numbers have matched.

Usually 8 numbers match only once a year or maybe not. prize money for 8 numbers is 10k as above.

But there many times 4,5 or 6 numbers match.

For example to find out how many times 4numbers have matched, I have to scroll through for the entire year to find that out and that takes a very long time.

1) What formula can I use to find out how many times 4 numbers have matched so I can get the answer in one go.?

Each 4 numbers matched has prize money of $2.

2) what formula can I use to find out the total dollar value of the 4 numbers matched ?

https://filebin.net/kkiwhh8z3nsxif74

Thanks
simon

 

[AlanY]

Re: excel filter - formula

btw, have you turned off the auto calculation?

 

[simon31]

Re: excel filter - formula

File link below

https://filebin.net/ap8jiv07rqryjqlf

I have not touched any auto calculation option. I have done only the steps that was asked to be followed.

Anyway since I have uploaded the file , you could detect if there is any descrepancy

 

[AlanY]

Re: excel filter - formula

File link below

https://filebin.net/ap8jiv07rqryjqlf

I have not touched any auto calculation option. I have done only the steps that was asked to be followed.

Anyway since I have uploaded the file , you could detect if there is any descrepancy

i think i may know what happened, just to clear if you click the update arrow on that saved file above, will you still get 12 lines of result?

 

[simon31]

Re: excel filter - formula

yes I clicked on the udpate arrow again on the save file and it still gives only 12 lines of results

 

[AlanY]

Re: excel filter - formula

finally got some time to look at it again but unfortunately can't recreate the 12 lines result.
anyway i'd revamp the file to do it differently and may not do all the things you asked for.

https://drive.google.com/file/d/1Xtyf9APSxWYfaEcUB_OT1SpckNeULhDc/view?usp=sharing

1) with the 10 winning Nos in N3:W3, let pick all the draws between 9/6/2019 to 22/6/2019 and click 'update'

<img src="https://www.pixelsbin.com/images/2019/07/30/1e7982cc851228a3b.jpg" alt="1e7982cc851228a3b.jpg" border="0">

2) that will highlighted 41 draws (Cell F5) has 3 or more matching Nos

<img src="https://www.pixelsbin.com/images/2019/07/30/2d46e77e893177a14.jpg" alt="2d46e77e893177a14.jpg" border="0">

3) from then on you have to eyeball the hot numbers within the 41 draws, e.g. 24 & 25 look promising so you can check them by input 24 & 25 in Cells N5:O5 and click 'check'. That shows 6 draws with both 24 & 25

<img src="https://www.pixelsbin.com/images/2019/07/30/3bc71680fc895c9b0.jpg" alt="3bc71680fc895c9b0.jpg" border="0">

 

[AlanY]

Re: excel filter - formula

4) again by eyeballing the results, you can try 66 and click 'check' again, that shows 2 draws with 24 25 and 66

<img src="https://www.pixelsbin.com/images/2019/07/30/4ddbfae19c27b195a.jpg" alt="4ddbfae19c27b195a.jpg" border="0">

5) to check other combinations with 24 and 25, first delete 66 and click 'check' again, the same 6 lines with 24 25 here again, let say you put 7 in for checking and you get 4 draws with 24 25 & 7.

<img src="https://www.pixelsbin.com/images/2019/07/30/51fc2c5d00be51673.jpg" alt="51fc2c5d00be51673.jpg" border="0">

 

[simon31]

Re: excel filter - formula

the steps have worked exactly the way you have shown.

And coming back to the date period between 9th June and 22 June, by eyeballing I came to know that the 4 numbers (22, 31 34 36) hit 3 times during that date period. I was hoping that you would be able to replace eyeballing with a formula that would give the results for the most hit numbers for 4 group and 5 group numbers.

And so eyeballing is exactly what I was trying to avoid.

Can eyeballing be replaced with a formula to get quick display of results of most frequent 4 group and 5 group numbers ?

 

[AlanY]

Re: excel filter - formula

I do realise what you're after, I think.

Let me tell what I'm trying to do. with the numbers from 1 to 80 there are 82160 combinations of 3 No set.
I can't think of any workable algorithm apart from a brute force attack, i.e. check them out 1 by 1.

Now, for a 7 days period with 28 draws that took just over 40 mins on my computer. Well, a faster machine may cut it down to 35 or 30 mins.

Another option is to pick a 'hot' number then let the machine check the combinations of the other 2, that cut down the calculation to 80s.
that 'hot' number atm automatically pick out the most frequent within the draws, or can be specify by the user.

tell me what you think.

 

[simon31]

Re: excel filter - formula

Another option is to pick a 'hot' number then let the machine check the combinations of the other 2, that cut down the calculation to 80s.
that 'hot' number atm automatically pick out the most frequent within the draws, or can be specify by the user.

can you please explain with example how to do it ?

 

[AlanY]

Re: excel filter - formula

right, here we go.

https://drive.google.com/file/d/1bh6_bbyO8SHXV0S8vqNd5TxUMekbnoMm/view?usp=sharing

I'm going to show you how it works with a small sample from 16/06/2019 to 22/06/2019 3pm

1) start with the update button you should get this, 4 out of the 7 draws has 3 or more nos matched

<img src="https://www.pixelsbin.com/images/2019/08/05/153867c97e48670d5.jpg" alt="153867c97e48670d5.jpg" border="0">

2) on the new 'Hot numbers' sheet there are 2 buttons with 3 options;

a) 'Hot 3' will work out all the 3 nos groups that matched, i.e. all the 1-80 nos combinations
b) 'Hot 2+1' similar but with 1 pre-fixed, either specify by yourself or excel will use the most frequent no within the date set.

we will start with b) above first, with no number in Cell N5 on the front sheet, click 'Hot 2=1'
excel will use 25 as one of the most frequent no (Col C) and work out all combinations of the other 2 (Cols A & B)
with the results (Frequency) on Col D.
you can see the max freq is 2 for a few combinations, 4, 38; 4, 60 etc and 25.

<img src="https://www.pixelsbin.com/images/2019/08/05/36b863e18e2e12528.jpg" alt="36b863e18e2e12528.jpg" border="0">

3) you also pick one of your own number on N5 like this and hit 'Hot 2=1' again,

<img src="https://www.pixelsbin.com/images/2019/08/05/4cc17a0b24ec855e8.jpg" alt="4cc17a0b24ec855e8.jpg" border="0">

4) with another set of results like this

<img src="https://www.pixelsbin.com/images/2019/08/05/55251892079821427.jpg" alt="55251892079821427.jpg" border="0">