Hi MrExcel member
this is my first post, my name is ragwi i live in indonesia.
past few month i just make worksheet to play pick 3 or pick 4 games. right now i can't find any simple formula to improve the worksheet, i guess this time i must learn vba which is not userfriendly to me. btw i'm rookie about worksheet, only knew some simple formula.
also i think the worksheet reach the limit, when i create large drawing files or morethan 4000 draw, "calculate" message always appear.
i don't know which worksheet specifications and limits, maybe too many formula and calculation lol
and some simple formula that i use for my worksheet.
to calculate hit:
=COUNTIF(draw!$F$2:$F$65535,C31)
to calculate skips:
=(COUNTA(draw!$F$2:$F$65535)+6)-(65535-(ROWS(draw!$F$2:$F$65535)-MATCH(2,1/SEARCH(C31,draw!$F$2:$F$65535))))
manual permutation of frequency distribution
i use this formula
=IF(G19="","",IF(G20="","",IF(G21="","",G19&G20&G21)))
to make six number permutation, for example
0 0 7 8 blank blank bkank
1 1 3 blank blank blank blank
2 5 6 9 blank blank blank
3 2 blank blank blank blank blank
4 4 blank blank blank blank blank
5 blank blank blank blank blank blank
from sample above, the result like this
single FD combination 3D No.
012 078 ~ 13 ~ 569 015 016 019 035 036 039 715 716 719 735 736 739 815 816 819 835 836 839
013 078 ~ 13 ~ 2 012 032 712 732 812 832
014 078 ~ 13 ~ 4 014 034 714 734 814 834
015
023 078 ~ 569 ~ 2 052 062 092 752 762 792 852 862 892
024 078 ~ 569 ~ 4 054 064 094 754 764 794 854 864 894
025
034 078 ~ 2 ~ 4 024 724 824
035
045
123 13 ~ 569 ~ 2 152 162 192 352 362 392
124 13 ~ 569 ~ 4 154 164 194 354 364 394
125
134 13 ~ 2 ~ 4 124 324
135
145
234 569 ~ 2 ~ 4 524 624 924
235
245
345
double FD combination 3D No.
001 078 ~ 078 ~ 13 001 003 071 073 081 083 701 703 771 773 781 783 801 803 871 873 881 883
002 078 ~ 078 ~ 569 005 006 009 075 076 079 085 086 089 705 706 709 775 776 779 785 786 789 805 806 809 875 876 879 885 886 889
003 078 ~ 078 ~ 2 002 072 082 702 772 782 802 872 882
004 078 ~ 078 ~ 4 004 074 084 704 774 784 804 874 884
005
011 078 ~ 13 ~ 13 011 013 031 033 711 713 731 733 811 813 831 833
022 078 ~ 569 ~ 569 055 056 059 065 066 069 095 096 099 755 756 759 765 766 769 795 796 799 855 856 859 865 866 869 895 896 899
033 078 ~ 2 ~ 2 022 722 822
044 078 ~ 4 ~ 4 044 744 844
055
112 13 ~ 13 ~ 569 115 116 119 135 136 139 315 316 319 335 336 339
113 13 ~ 13 ~ 2 112 132 312 332
114 13 ~ 13 ~ 4 114 134 314 334
115
122 13 ~ 569 ~ 569 155 156 159 165 166 169 195 196 199 355 356 359 365 366 369 395 396 399
133 13 ~ 2 ~ 2 122 322
144 13 ~ 4 ~ 4 144 344
155
223 569 ~ 569 ~ 2 552 562 592 652 662 692 952 962 992
224 569 ~ 569 ~ 4 554 564 594 654 664 694 954 964 994
225
233 569 ~ 2 ~ 2 522 622 922
244 569 ~ 4 ~ 4 544 644 944
255
334 2 ~ 2 ~ 4 224
335
344 2 ~ 4 ~ 4 244
355
445
455
triple FD combination 3D No.
000 078 ~ 078 ~ 078 000 007 008 070 077 078 080 087 088 700 707 708 770 777 778 780 787 788 800 807 808 870 877 878 880 887 888
111 13 ~ 13 ~ 13 111 113 131 133 311 313 331 333
222 569 ~ 569 ~ 569 555 556 559 565 566 569 595 596 599 655 656 659 665 666 669 695 696 699 955 956 959 965 966 969 995 996 999
333 2 ~ 2 ~ 2 222
444 4 ~ 4 ~ 4 444
555
to calculate pair i use this formula
=IF(G19="","",IF(G19="","",G19&G19))
the result like this
PAIR Frequency Distribution
11 combination pair number
00 078 ~ 078 00 07 08 70 77 78 80 87 88
11 13 ~ 13 11 13 31 33
22 569 ~ 569 55 56 59 65 66 69 95 96 99
33 2 ~ 2 22
44 4 ~ 4 44
55
12 combination pair number
01 078 ~ 13 01 03 71 73 81 83
02 078 ~ 569 05 06 09 75 76 79 85 86 89
03 078 ~ 2 02 72 82
04 078 ~ 4 04 74 84
05
12 13 ~ 569 15 16 19 35 36 39
13 13 ~ 2 12 32
14 13 ~ 4 14 34
15
23 569 ~ 2 52 62 92
24 569 ~ 4 54 64 94
25
34 2 ~ 4 24
35
45
21 combination pair number
10 13 ~ 078 10 30 17 37 18 38
20 569 ~ 078 50 60 90 57 67 97 58 68 98
30 2 ~ 078 20 27 28
40 4 ~ 078 40 47 48
50
21 569 ~ 13 51 61 91 53 63 93
31 2 ~ 13 21 23
41 4 ~ 13 41 43
51
32 2 ~ 569 25 26 29
42 4 ~ 569 45 46 49
52
43 4 ~ 2 42
53
54
PAIR Hot Average Cold
HAC combination pair number
AA 3567 ~ 3567 35 36 37 56 57 67 53 63 73 65 75 76 33 55 66 77
CC 014 ~ 014 01 04 14 10 40 41 00 11 44
HH 289 ~ 289 28 29 89 82 92 98 22 88 99
AC 3567 ~ 014 30 31 34 50 51 54 60 61 64 70 71 74
AH 3567 ~ 289 32 38 39 52 58 59 62 68 69 72 78 79
CH 014 ~ 289 02 08 09 12 18 19 42 48 49
CA 014 ~ 3567 03 13 43 05 15 45 06 16 46 07 17 47
HA 289 ~ 3567 23 83 93 25 85 95 26 86 96 27 87 97
HC 289 ~ 014 20 80 90 21 81 91 24 84 94
to calculate rank order
=COUNT(C6:C15)-(RANK(C6,C6:C15)+COUNTIF(C6:C6,C6))+2
and here is THE SIZE PROBLEM
to count interval for each patterns i use this formula
=IF(OR(((COUNTA(draw!$AA$2:$AA3)+1)-((COUNTA(draw!$AA$2:$AA3)+1)-(ROWS(draw!$AA$2:$AA3)-MATCH(2,1/SEARCH(B$1,draw!$AA$2:$AA3)))))=0,((COUNTA(draw!$AA$2:$AA2)+1)-((COUNTA(draw!$AA$2:$AA2)+1)-(ROWS(draw!$AA$2:$AA2)-MATCH(2,1/SEARCH(B$1,draw!$AA$2:$AA2)))))=0),((COUNTA(draw!$AA$2:$AA2)+1)-((COUNTA(draw!$AA$2:$AA2)+1)-(ROWS(draw!$AA$2:$AA2)-MATCH(2,1/SEARCH(B$1,draw!$AA$2:$AA2))))),"")
this formula will count last hit and skip before last hit, 00 is hit
00 10 00 03 00 08 00 01 00 02 00 03 00 00 02 00 01 00
well i need help because size and process will be bigger and longer which is against rules of simplicity to play pick 3 / pick4 games lol
i though when we play this numbers we must spend maximum 60 minutes to get next draws more than a hour you should not play pick 3/4 games
eg for 50 draws the worksheet take 10.3 MB
if any body interested with the worksheet to make more simple, smaller and faster pm me and give me your email address.
and i think to make more simple, smaller and faster it comes to VBA which is i'm green about vba.
thank,
ragwi
this is my first post, my name is ragwi i live in indonesia.
past few month i just make worksheet to play pick 3 or pick 4 games. right now i can't find any simple formula to improve the worksheet, i guess this time i must learn vba which is not userfriendly to me. btw i'm rookie about worksheet, only knew some simple formula.
also i think the worksheet reach the limit, when i create large drawing files or morethan 4000 draw, "calculate" message always appear.
i don't know which worksheet specifications and limits, maybe too many formula and calculation lol
and some simple formula that i use for my worksheet.
to calculate hit:
=COUNTIF(draw!$F$2:$F$65535,C31)
to calculate skips:
=(COUNTA(draw!$F$2:$F$65535)+6)-(65535-(ROWS(draw!$F$2:$F$65535)-MATCH(2,1/SEARCH(C31,draw!$F$2:$F$65535))))
manual permutation of frequency distribution
i use this formula
=IF(G19="","",IF(G20="","",IF(G21="","",G19&G20&G21)))
to make six number permutation, for example
0 0 7 8 blank blank bkank
1 1 3 blank blank blank blank
2 5 6 9 blank blank blank
3 2 blank blank blank blank blank
4 4 blank blank blank blank blank
5 blank blank blank blank blank blank
from sample above, the result like this
single FD combination 3D No.
012 078 ~ 13 ~ 569 015 016 019 035 036 039 715 716 719 735 736 739 815 816 819 835 836 839
013 078 ~ 13 ~ 2 012 032 712 732 812 832
014 078 ~ 13 ~ 4 014 034 714 734 814 834
015
023 078 ~ 569 ~ 2 052 062 092 752 762 792 852 862 892
024 078 ~ 569 ~ 4 054 064 094 754 764 794 854 864 894
025
034 078 ~ 2 ~ 4 024 724 824
035
045
123 13 ~ 569 ~ 2 152 162 192 352 362 392
124 13 ~ 569 ~ 4 154 164 194 354 364 394
125
134 13 ~ 2 ~ 4 124 324
135
145
234 569 ~ 2 ~ 4 524 624 924
235
245
345
double FD combination 3D No.
001 078 ~ 078 ~ 13 001 003 071 073 081 083 701 703 771 773 781 783 801 803 871 873 881 883
002 078 ~ 078 ~ 569 005 006 009 075 076 079 085 086 089 705 706 709 775 776 779 785 786 789 805 806 809 875 876 879 885 886 889
003 078 ~ 078 ~ 2 002 072 082 702 772 782 802 872 882
004 078 ~ 078 ~ 4 004 074 084 704 774 784 804 874 884
005
011 078 ~ 13 ~ 13 011 013 031 033 711 713 731 733 811 813 831 833
022 078 ~ 569 ~ 569 055 056 059 065 066 069 095 096 099 755 756 759 765 766 769 795 796 799 855 856 859 865 866 869 895 896 899
033 078 ~ 2 ~ 2 022 722 822
044 078 ~ 4 ~ 4 044 744 844
055
112 13 ~ 13 ~ 569 115 116 119 135 136 139 315 316 319 335 336 339
113 13 ~ 13 ~ 2 112 132 312 332
114 13 ~ 13 ~ 4 114 134 314 334
115
122 13 ~ 569 ~ 569 155 156 159 165 166 169 195 196 199 355 356 359 365 366 369 395 396 399
133 13 ~ 2 ~ 2 122 322
144 13 ~ 4 ~ 4 144 344
155
223 569 ~ 569 ~ 2 552 562 592 652 662 692 952 962 992
224 569 ~ 569 ~ 4 554 564 594 654 664 694 954 964 994
225
233 569 ~ 2 ~ 2 522 622 922
244 569 ~ 4 ~ 4 544 644 944
255
334 2 ~ 2 ~ 4 224
335
344 2 ~ 4 ~ 4 244
355
445
455
triple FD combination 3D No.
000 078 ~ 078 ~ 078 000 007 008 070 077 078 080 087 088 700 707 708 770 777 778 780 787 788 800 807 808 870 877 878 880 887 888
111 13 ~ 13 ~ 13 111 113 131 133 311 313 331 333
222 569 ~ 569 ~ 569 555 556 559 565 566 569 595 596 599 655 656 659 665 666 669 695 696 699 955 956 959 965 966 969 995 996 999
333 2 ~ 2 ~ 2 222
444 4 ~ 4 ~ 4 444
555
to calculate pair i use this formula
=IF(G19="","",IF(G19="","",G19&G19))
the result like this
PAIR Frequency Distribution
11 combination pair number
00 078 ~ 078 00 07 08 70 77 78 80 87 88
11 13 ~ 13 11 13 31 33
22 569 ~ 569 55 56 59 65 66 69 95 96 99
33 2 ~ 2 22
44 4 ~ 4 44
55
12 combination pair number
01 078 ~ 13 01 03 71 73 81 83
02 078 ~ 569 05 06 09 75 76 79 85 86 89
03 078 ~ 2 02 72 82
04 078 ~ 4 04 74 84
05
12 13 ~ 569 15 16 19 35 36 39
13 13 ~ 2 12 32
14 13 ~ 4 14 34
15
23 569 ~ 2 52 62 92
24 569 ~ 4 54 64 94
25
34 2 ~ 4 24
35
45
21 combination pair number
10 13 ~ 078 10 30 17 37 18 38
20 569 ~ 078 50 60 90 57 67 97 58 68 98
30 2 ~ 078 20 27 28
40 4 ~ 078 40 47 48
50
21 569 ~ 13 51 61 91 53 63 93
31 2 ~ 13 21 23
41 4 ~ 13 41 43
51
32 2 ~ 569 25 26 29
42 4 ~ 569 45 46 49
52
43 4 ~ 2 42
53
54
PAIR Hot Average Cold
HAC combination pair number
AA 3567 ~ 3567 35 36 37 56 57 67 53 63 73 65 75 76 33 55 66 77
CC 014 ~ 014 01 04 14 10 40 41 00 11 44
HH 289 ~ 289 28 29 89 82 92 98 22 88 99
AC 3567 ~ 014 30 31 34 50 51 54 60 61 64 70 71 74
AH 3567 ~ 289 32 38 39 52 58 59 62 68 69 72 78 79
CH 014 ~ 289 02 08 09 12 18 19 42 48 49
CA 014 ~ 3567 03 13 43 05 15 45 06 16 46 07 17 47
HA 289 ~ 3567 23 83 93 25 85 95 26 86 96 27 87 97
HC 289 ~ 014 20 80 90 21 81 91 24 84 94
to calculate rank order
=COUNT(C6:C15)-(RANK(C6,C6:C15)+COUNTIF(C6:C6,C6))+2
and here is THE SIZE PROBLEM
to count interval for each patterns i use this formula
=IF(OR(((COUNTA(draw!$AA$2:$AA3)+1)-((COUNTA(draw!$AA$2:$AA3)+1)-(ROWS(draw!$AA$2:$AA3)-MATCH(2,1/SEARCH(B$1,draw!$AA$2:$AA3)))))=0,((COUNTA(draw!$AA$2:$AA2)+1)-((COUNTA(draw!$AA$2:$AA2)+1)-(ROWS(draw!$AA$2:$AA2)-MATCH(2,1/SEARCH(B$1,draw!$AA$2:$AA2)))))=0),((COUNTA(draw!$AA$2:$AA2)+1)-((COUNTA(draw!$AA$2:$AA2)+1)-(ROWS(draw!$AA$2:$AA2)-MATCH(2,1/SEARCH(B$1,draw!$AA$2:$AA2))))),"")
this formula will count last hit and skip before last hit, 00 is hit
00 10 00 03 00 08 00 01 00 02 00 03 00 00 02 00 01 00
well i need help because size and process will be bigger and longer which is against rules of simplicity to play pick 3 / pick4 games lol
i though when we play this numbers we must spend maximum 60 minutes to get next draws more than a hour you should not play pick 3/4 games
eg for 50 draws the worksheet take 10.3 MB
if any body interested with the worksheet to make more simple, smaller and faster pm me and give me your email address.
and i think to make more simple, smaller and faster it comes to VBA which is i'm green about vba.
thank,
ragwi
