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 - cell F5 value ?

image one



cell F5 reads 1 time, but the 4 group numbers (22, 31 34 36 ) has come 3 times within the period of 16 June to 22June
so F5 is not giving the answer as 3, but 1 which is incorrect.

that was the mistake in the Cell F5 formula, fixed with the latest version

 

[simon31]

Re: excel filter - excel file

Reattached excel file with update till 22June

https://filebin.net/ya6wth10zhz2ik99

If you click directly on the file , it will download all xml files and other files.

Please click only on the download file button on the filebin website, then only the excel file will download.

I don't have your upload with the cell F5 fix ?

 

[AlanY]

Re: excel filter - excel file

the latest version I've added the top 10 winning nos and the frequencies within the date/time range just beneath the winning nos/frequencies, F5 fixed.
the spreadsheet is running a bit slower as lots of calculations in the background

https://drive.google.com/file/d/1lCiU2nu-3c1ZlP5_bnyRd-b_0-GrIzu0/view?usp=sharing

<img src="https://www.pixelsbin.com/images/2019/06/24/1f0e004798a6b9914.jpg" alt="1f0e004798a6b9914.jpg" border="0">

<img src="https://www.pixelsbin.com/images/2019/06/24/2ad872abf3b8f9e97.jpg" alt="2ad872abf3b8f9e97.jpg" border="0">

 

[simon31]

Re: excel filter - excel file

how do I generate the top 10 results ? bit confused here.

Above snapshot shows errors as well. How do I test the last 7 days top 10. ?

 

[AlanY]

Re: excel filter - excel file

after you input the dates and click the filter button, the top 10 should be updated as

<img src="https://www.pixelsbin.com/images/2019/06/24/36f054cc082b0334e.jpg" alt="36f054cc082b0334e.jpg" border="0">

 

[AlanY]

Re: excel filter - excel file

at the moment the top 10 are the numbers appeared within the 20 numbers each draw.
is that what you're after?

 

[simon31]

Re: excel filter - excel file

If I understand correctly row 5 is independent of row 3 or are they dependent on each other ? or do I have input numbers in row 3 for row 5 to populate ?

 

[simon31]

Re: excel filter - excel file

Regarding the combination numbers frequency, I scanned through visually for the period 16June to 22 June (inside the 20hits) and found out that the numbers 22 31 34 36 had hit 3 times as a group of four numbers.

But if I want excel to do that automatically how would you make excel find out how many 3 numbers group, 4 numbers group, 5 numbers group,etc hit during the period of 16June to 22 June as a test example.?

 

[AlanY]

Re: excel filter - excel file

If I understand correctly row 5 is independent of row 3 or are they dependent on each other ? or do I have input numbers in row 3 for row 5 to populate ?

may be i'm confused with how the Keno works, am i right to assume that

1) there are 20 random numbers from 1-80 each draw
2) from that 10 are picked randomly as winning nos
3) and players and pick up 3, 4 etc from those 10

as the spreadsheet stands at the moment

the filters worked the number of the winning 10 nos (Cell M3) within the 20 numbers of each draw.

the top 10 are independent of the 10 winning nos in Row 3, they are the top numbers appears within the date/time setup in Row 1

 

[AlanY]

Re: excel filter - excel file

Regarding the combination numbers frequency, I scanned through visually for the period 16June to 22 June (inside the 20hits) and found out that the numbers 22 31 34 36 had hit 3 times as a group of four numbers.

But if I want excel to do that automatically how would you make excel find out how many 3 numbers group, 4 numbers group, 5 numbers group,etc hit during the period of 16June to 22 June as a test example.?

if you put the 4 no group in row 3 you should get the 3 hits automatically with the filter button, 3 or 4 numbers should work just as same

<img src="https://www.pixelsbin.com/images/2019/06/24/52dbe8f12a43a8ef8.jpg" alt="52dbe8f12a43a8ef8.jpg" border="0">

<img src="https://www.pixelsbin.com/images/2019/06/24/4a073cc259140e725.jpg" alt="4a073cc259140e725.jpg" border="0">