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 - missing out on the time column

the latest version here

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

I've made a few assumptions for the draw times as follow

1) for the 4 draws per day, assumed the top line is 10am .. and the 4th line is 6pm,
2) there are only 2 draws/day between 24 Aug 2014 and 11 May 2004, assumed draw times are 1pm and 6pm,
3) prior to 11 May 2004 mainly 1 draw/day, assumed at 6pm, some have 2 draws/day (e.g. 1 Jan 2004, 3 Aug 2003 etc) again assumed at 1 & 6pm.

You can alter the excel zoom at the bottom right corner to fit onto your screen

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

 

[simon31]

Re: excel filter - draw times orientation in the time column

I was trying to explain about the draw times orientation via snapshots but since I don't have your website link which you gave me last time ( I forgot the website name) for inserting snapshots into this forum , I will have to try to explain by writing.

Draw times orientation : -
As an example, Monday 17 June 2019 has the following draw time orientation.

17163.........6 pm
17162........ 3 pm
17161........1 pm
17160 ........10am.

The orientation you have provided in your file for the 4 draws is not in the right order at the moment. So the filter results would turn out to be incorrect.
Top line should be 6 pm and 4th line should be 10 am.

When there were two draws as you mentioned, the draw times were 10 am and 6pm only.
but the order for the two draws would also be top line 6pm and second line 10 am ( for 2 draws only).

I don't know the draw time when it was just a single draw per day prior to 11 May 2004. I will investigate that with the keno website support staff and get back to you.

 

[AlanY]

Re: excel filter - draw times orientation in the time column

times are corrected for the 2/4 draws/day, and the 1 draw/day is assumed at 6pm for this latest version

https://drive.google.com/file/d/18oHvvuaFuwrPrbHnWVXQE59nqz3k3uPn/view?usp=sharing

 

[simon31]

Re: excel filter - very impressed with time filter in cell V2

Thanks for fixing the draw times orientation.

I was very impressed with the time filter in cell V2 in viewing range and also F5 and M5. You have improved the keno table leaps and bounds.

In order to play smarter though, I have been seeing the hot numbers hits within 4 days to a week coming too often and I am unable to know which are they in the 20 number group. I have to keep checking manually which 3 numbers / 4 numbers buddied each other in the most recent 7 days of the draws.

For example I was looking for a 3 hot number group and 4 hot number group (or buddies of each other) so that I could play one of those before those numbers turn cold after a week.

3 numbers payout is $20 and 4 numbers payout is $150.

While I can see the the frequency of the single number example 41 in the winning numbers group, I don’t know which are the other hot two buddies in the 3 number group and 4 number group that come too many times with the 7 day period. I have to keep doing trial and error to find out which takes quite a bit of time.

Is there a formula that can capture the hot numbers information of last 7 days draw to show the most frequent numbers of 3 number group(buddies) and 4 number group(buddies).?

 

[AlanY]

Re: excel filter - very impressed with time filter in cell V2

I've added formulas in Cells N6:W6 that shown the frequency of the number above in row 5 within the date ranges and draw time

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

 

[simon31]

Re: excel filter - cell F5 ?

inserting snapshot failed. ImageOne attached

Could not attach snapshot as usual, its always a struggle, so I hope my explanation below is clear. If explanation is unclear I am unable to do much if I cannot insert snapshots.

In snapshot above, Cell F5 is reading incorrect number as 1,instead of 4 times.
I don't know why ?

Thanks for adding row 6 for number frequency as that is very handy.

In the snapshot below you can see the different frequencies in row 6 for numbers in row 5.
the numbers

22 hit 7 times,31 hit 8 times,34 hit 6 times, 36 hit 8 times. Since the 4 numbers are different frequency I would not know whether to use them as combination to play those one more time.

inserting snapshot failed. image two attached

That is why I was also looking for a formula to capture 3 group numbers and 4 group numbers.

In the snapshot you see, 4 group numbers came three times between 16 and 22 June.
I got this 4 number information by viewing manually for about 15 minutes. So if a formula can do the same thing in seconds would be appreciated.

I have attached the latest file with results updated until 22 June.

I carried the above test for the period 16 June to 22 June. If you please test out the same period with the updated file. thanks

Your file updated with the latest results attached

 

[simon31]

Re: excel filter - cell F5 ?

image one attached. https://filebin.net/mxilqwsswpsgya4l

image two attached. https://filebin.net/2w5060by7rnm2dwz

Your file attached with results updated till 22 June. https://filebin.net/yinpdhrm914h6n02

 

[simon31]

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.

image two



In the snapshot you see, 4 group numbers came three times between 16 and 22 June.
I got this 4 number information by viewing manually for about 15 minutes. So if a formula can do the same thing in seconds would be appreciated.


I have attached the latest file with results updated until 22 June.


I carried the above test for the period 16 June to 22 June. If you please test out the same period with the updated file. thanks


Your file updated with the latest results attached

 

[simon31]

Re: excel filter - cell F5 value ?

In the imagetwo snapshot , you can see the different frequencies in row 6 for numbers in row 5.




22 hit 7 times,31 hit 8 times,34 hit 6 times, 36 hit 8 times. (22 31 34 36). Since the 4 numbers are of different frequency I would not know whether to use them as combination numbers to play those one more time before it gets cold.


That is why I was also looking for a formula that can capture maximum 3 group numbers and 4 group numbers that came within last 7 days.

I have struggled the hard way and found out an external website that can paste snapshot images into this post. For all those struggling similarly, the answer is your image should be embedded as a link and then you paste the link in the post and then click on submit reply, then when the screen refreshes , bingo you see the snapshot image.

 

[AlanY]

Re: excel filter - cell F5 ?

image one attached. https://filebin.net/mxilqwsswpsgya4l

image two attached. https://filebin.net/2w5060by7rnm2dwz

Your file attached with results updated till 22 June. https://filebin.net/yinpdhrm914h6n02

the file attached is not an excel file but a folder with lots of .xml files that I can't use it with excel.
could you re-post the excel file with the latest results so that I can test them