Welcome Guest
Log In | Register )
You last visited December 11, 2016, 4:15 am
All times shown are
Eastern Time (GMT-5:00)

need excel guru & master

Topic closed. 24 replies. Last post 11 years ago by SirMetro.

Page 1 of 2
PrintE-mailLink
sysp34's avatar - Lottery-062.jpg
Heroic City
Indonesia
Member #31689
February 2, 2006
1153 Posts
Offline
Posted: February 3, 2006, 5:25 am - IP Logged

Dear Lottery Post Member, guru & excel master

please help me with macros cause i don't familiar with those step,
only a simple function :)

the file sysp34.xls are sunday draw with month febuary from 1987 (SG4D lottery)

download link:

zip format
http://www.rapidupload.com/d.php?file=dl&filepath=9720
excel format
http://www.rapidupload.com/d.php?file=dl&filepath=9721


please copy the link& paste to your internet browser

it's need a lot of step to all lottery state, please help me so all pick 3 hobbiest can use these tools.

number reference
odd even
Odd = 1-3-5-7-9
Even = 0-2-4-6-8

low high
Low = 0-1-2-3-4
High = 5-6-7-8-9

A = OddLow   = 1-3
B = EvenLow = 0-2-4
C = OddHigh = 5-7-9
D = EvenHigh = 6-8

A = 0 - 1
B = 2 - 3
C = 4 - 5
D = 6 - 7
E = 8 - 9

here are the formula at sysp34.xls

vlook for root sum two digit (2D) = column A3 to 102

C3 = date
D3 = result
E3 = 1st
F3 = 2nd
G3 = 3rd
H3 = 4th

sorted 4D/3d (courtesy of JADE LOTTERY)
I3 =IF(E3="x"," ",MIN(E3,F3,G3,H3))
J3 =IF(E3="x"," ",(((SUM(E3+1,F3+1,G3+1,H3+1)-MIN(E3+1,F3+1,G3+1,H3+1)-MAX(E3+1,F3+1,G3+1,H3+1))-SQRT(((SUM(E3+1,F3+1,G3+1,H3+1)-MIN(E3+1,F3+1,G3+1,H3+1)-MAX(E3+1,F3+1,G3+1,H3+1))^2)-4*(PRODUCT(E3+1,F3+1,G3+1,H3+1)/(MIN(E3+1,F3+1,G3+1,H3+1)*MAX(E3+1,F3+1,G3+1,H3+1)))))/2)-1)
K3 =IF(E3="x"," ",(((SUM(E3+1,F3+1,G3+1,H3+1)-MIN(E3+1,F3+1,G3+1,H3+1)-MAX(E3+1,F3+1,G3+1,H3+1))+SQRT(((SUM(E3+1,F3+1,G3+1,H3+1)-MIN(E3+1,F3+1,G3+1,H3+1)-MAX(E3+1,F3+1,G3+1,H3+1))^2)-4*(PRODUCT(E3+1,F3+1,G3+1,H3+1)/(MIN(E3+1,F3+1,G3+1,H3+1)*MAX(E3+1,F3+1,G3+1,H3+1)))))/2)-1)
L3 =IF(E3="x"," ",MAX(E3,F3,G3,H3))

M3 = lookup 2D Back Pair
N3 = result relationship

O to X = monthly frequency; i use =COUNTIF($E3:$H6,O$2) (for $E3:$H6 replace with monthly)

Y to AB = for filter low high; i use =IF(E3="x"," ",IF(OR(E3=0,E3=1,E3=2,E3=3,E3=4),"L","H"))
AC to AF = for filter even odd; i use =IF(E3="x"," ",IF(OR(E3=1,E3=3,E3=5,E3=7,E3=9),"O","E"))
AG to AJ = for direct oddlow, oddhigh, evenlow, evenhigh; =Y3&"_"&AC3

AK3 = for pairing 12xx; i use =VLOOKUP(VALUE(E3&F3),$A$3:$B$102,2)
AL3 = for pairing x23x; i use =VLOOKUP(VALUE(F3&G3),$A$3:$B$102,2)
AM3 = for pairing xx34; i use =VLOOKUP(VALUE(G3&H3),$A$3:$B$102,2)
AN3 = for pairing 1x3x; i use =VLOOKUP(VALUE(E3&G3),$A$3:$B$102,2)
AO3 = for pairing x2x4; i use =VLOOKUP(VALUE(F3&H3),$A$3:$B$102,2)
AP3 = for pairing 1xx4; i use =VLOOKUP(VALUE(E3&H3),$A$3:$B$102,2)

AQ to BZ to search sum of 2D for 12xx..x23x..xx34..1x3x..x2x4..1xx4
with range 0 - 3, 4 - 6, 7 - 9, 10 - 12, 13 - 15, 16 - 18

AQ to AV = sum of 2D for 12xx;
AQ3 = range   0 - 3, i use =IF(OR($AK3=0,$AK3=1,$AK3=2,$AK3=3),"x"," ")
AR3 = range   4 - 6, i use =IF(OR($AK3=4,$AK3=5,$AK3=6),"x"," ")
AS3 = range   7 - 9, i use =IF(OR($AK3=7,$AK3=8,$AK3=9),"x"," ")
AT3 = range 10 - 12, i use =IF(OR($AK3=10,$AK3=11,$AK3=12),"x"," ")
AU3 = range 13 - 15, i use =IF(OR($AK3=13,$AK3=14,$AK3=15),"x"," ")
AV3 = range 16 - 18, i use =IF(OR($AK3=16,$AK3=17,$AK3=18),"x"," ")

AW to BB = sum of 2D for x23x;
AW3 =IF(OR($AL3=0,$AL3=1,$AL3=2,$AL3=3),"x"," ")
AX3 =IF(OR($AL3=4,$AL3=5,$AL3=6),"x"," ")
AY3 =IF(OR($AL3=7,$AL3=8,$AL3=9),"x"," ")
AZ3 =IF(OR($AL3=10,$AL3=11,$AL3=12),"x"," ")
BA3 =IF(OR($AL3=13,$AL3=14,$AL3=15),"x"," ")
BB3 =IF(OR($AL3=16,$AL3=17,$AL3=18),"x"," ")

BC to BH = sum of 2D for xx34; detail look at file sysp34.xls
BI to BN = sum of 2D for 1x3x; detail look at file sysp34.xls
BO to BT = sum of 2D for x2x4; detail look at file sysp34.xls
BU to BZ = sum of 2D for 1xx4; detail look at at file sysp34.xls

CB to CI monthly sum for filter LOW HIGH
CB3 =COUNTIF($Y3:$Y6,CB$2)
CC3 =COUNTIF($Y3:$Y6,CC$2)

column CK to CR monthly sum for filter ODD EVEN
CK3 =COUNTIF($AC3:$AC6,CK$2)
CL3 =COUNTIF($AC3:$AC6,CL$2)

column CT to DI montly sum for filter direct odd-low, odd-high, even-low, even-high

column DK to ET montly sum for 12xx..x23x..xx34..1x3x..x2x4..1xx4

the main analysis are:

* filter LOW-HIGH, EVEN-ODD, FILTER Low_Odd, High_Odd, Low_Even, High_Even
* HOT & DUE NUMBER
* win percentage rank
* Overall Number Frequency to make wheeling
* Number Statistics
* last 5 draw
* you can combine with pro filter pick3 (courtesy of stoopendaal)

NOTE: the 12xx..x23x..xx34..x2x3x..1x3x..1xx4 for pairing only but there are too many number i for reference only

poems for pick 3 :)

If there is 10 numbers with ABC, there will 720 numbers.
If there is 720 numbers with 2E_1O & 1E_2O, there will be 600 numbers.
If there is 600 numbers with 2L1H & 1L_2H, there will 492 numbers.
If there is 492 numbers with sum of 6 to 20, there will 486 numbers.
If there is 486 numbers with width of line 2 to 7, there will 390 numbers.
If there is 390 numbers with none repeat positions, there will 306 numbers.
If there is 306 numbers with dont allow draw from last 3284 , there will 11 numbers.
If there is 11 numbers with combo, there will be prize 3D somewhere.

0-7-2
1-3-8
3-4-7...3-5-8
4-3-9...4-7-8...4-8-5
6-2-1...6-3-1...6-4-9
8-1-2

please help me master & guru and good luck friends.

regards
sysp34

    lottaloot's avatar - AvatarZ56
    Redford/MI
    United States
    Member #3396
    January 18, 2004
    4867 Posts
    Offline
    Posted: February 3, 2006, 10:43 am - IP Logged

    WOW!  (says the Lootster with eyes big & glazed over)

     

    L ttaL   T

      time*treat's avatar - radar

      United States
      Member #13130
      March 30, 2005
      2171 Posts
      Offline
      Posted: February 3, 2006, 1:12 pm - IP Logged

      At worst, someone is asking for a whole bunch of gratis effort.

      At best, I am not understanding this post.

      In neo-conned Amerika, bank robs you.
      Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

        hypersoniq's avatar - 8ball
        Pennsylvania
        United States
        Member #1340
        April 6, 2003
        2450 Posts
        Offline
        Posted: February 4, 2006, 12:11 pm - IP Logged

        check out the .xls file

        it's quite complex... even more so than the description above...

        6 worksheets

        it will take some time just to figure this thing out... let alone whatever added functionality is requested...

         

        Playing more than one ticket per game is betting against yourself.

          LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
          Tx
          United States
          Member #4570
          May 4, 2004
          5180 Posts
          Offline
          Posted: February 5, 2006, 12:39 am - IP Logged

          WOW!  (says the Lootster with eyes big & glazed over)

           

          Same here !

          He's been "Lurking" for a while, I guess (?), Learning (?) or already knew (?), He is getting filters' stats with the spreadsheet, very good !, All is stats and their proper use, there is nothing else to use.

          A brain and programming skills together in the same person, he is dangerous. Just kidding Clown

          Good luck ! Lep

          BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

          "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

            Tenaj's avatar - michellea
            Charlotte NC
            United States
            Member #17406
            June 18, 2005
            4053 Posts
            Offline
            Posted: February 5, 2006, 12:56 am - IP Logged

            WOW!  (says the Lootster with eyes big & glazed over)

             

            ROFL

            takeemtothebank

              sysp34's avatar - Lottery-062.jpg
              Heroic City
              Indonesia
              Member #31689
              February 2, 2006
              1153 Posts
              Offline
              Posted: February 5, 2006, 1:22 am - IP Logged

              well the disadvantage Unhappy is the monthly filter must be create by your self (it take a time and attention)
              i can't create the MONTHLY or maybe WEEKLY filter (it must be manually) UnhappyUnhappyUnhappy 

              is there any master / anyone guide me to solve this step??? i have no idea

              help me please.

              regards
              sysp34

                LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
                Tx
                United States
                Member #4570
                May 4, 2004
                5180 Posts
                Offline
                Posted: February 5, 2006, 1:36 am - IP Logged

                Your filters are for doing Monthly and or Weekly predictions ?

                I only used filters when I did, back then, for predicting for the very next draw, not for many future draws as some others do here at the lottery post. I can program by the way and don't  even know how to use Exel, I have never tried to learn yet. HyperSoniq might help you with your Exel spreadsheet in time as he learns about it if he can, as to filters, well I only did them for the very next draw and no programming, for programming try Ricky (Paurths) and HyperSoniq.

                BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

                "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

                  LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
                  Tx
                  United States
                  Member #4570
                  May 4, 2004
                  5180 Posts
                  Offline
                  Posted: February 5, 2006, 1:39 am - IP Logged

                  Also, by the way, it is a little hard to understand you, which other language do you speak besides English (If any other) ?

                  BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

                  "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

                    sysp34's avatar - Lottery-062.jpg
                    Heroic City
                    Indonesia
                    Member #31689
                    February 2, 2006
                    1153 Posts
                    Offline
                    Posted: February 5, 2006, 2:04 am - IP Logged

                    Also, by the way, it is a little hard to understand you, which other language do you speak besides English (If any other) ?

                    i speak javanesse sorry if my english so bad Smile 

                    the file .xls design for monthly analysis.

                    i don't have any idea to create formula with week, or month conditions as a filter Unhappy because the pick 3 in my study will be "balance" (the number 0 to 9 "should" appears) the combination of monthly, weekly, and consecutive daily will be "a good" strategy to predict the next drawing.

                    is there any master or anyone guide me

                    regards
                    sysp34

                      powerplayer's avatar - Lottery-022.jpg

                      United States
                      Member #17834
                      June 28, 2005
                      2083 Posts
                      Offline
                      Posted: February 5, 2006, 4:36 am - IP Logged

                      Also, by the way, it is a little hard to understand you, which other language do you speak besides English (If any other) ?

                      i speak javanesse sorry if my english so bad Smile 

                      the file .xls design for monthly analysis.

                      i don't have any idea to create formula with week, or month conditions as a filter Unhappy because the pick 3 in my study will be "balance" (the number 0 to 9 "should" appears) the combination of monthly, weekly, and consecutive daily will be "a good" strategy to predict the next drawing.

                      is there any master or anyone guide me

                      regards
                      sysp34

                      sysp34,

                      Thank you for sharing your sheet. I have been able to use multiple formulas from the sheet.

                      Still looking over sheet. Nice work by the way!!

                      If I can help in anyway I will post for u.

                      Can u give a example of what your trying to change/do???

                      Thx

                      PP

                      Good luck to everyone!!!

                        paurths's avatar - underground
                        Switching between Fairfax, VA and Belgium
                        Belgium
                        Member #19287
                        July 29, 2005
                        2254 Posts
                        Offline
                        Posted: February 5, 2006, 9:02 am - IP Logged

                        At worst, someone is asking for a whole bunch of gratis effort.

                        At best, I am not understanding this post.

                        "gratis effort"?

                          time*treat's avatar - radar

                          United States
                          Member #13130
                          March 30, 2005
                          2171 Posts
                          Offline
                          Posted: February 5, 2006, 2:13 pm - IP Logged

                          gratis = free, but I did say "at worst".

                          In neo-conned Amerika, bank robs you.
                          Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

                            paurths's avatar - underground
                            Switching between Fairfax, VA and Belgium
                            Belgium
                            Member #19287
                            July 29, 2005
                            2254 Posts
                            Offline
                            Posted: February 5, 2006, 2:32 pm - IP Logged

                            gratis = free, but I did say "at worst".

                            lol

                            I know what it means, it's indeed the word for "free" in the language i speak.

                            I was just wondering how/why you would come up with it.

                              LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
                              Tx
                              United States
                              Member #4570
                              May 4, 2004
                              5180 Posts
                              Offline
                              Posted: February 5, 2006, 9:41 pm - IP Logged

                              It is also the word for free in Spanish. Which is a second language here in the USA. And so known by some non spanish people also.

                              BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

                              "Ten measures of beauty descended to the world, nine were taken by Jerusalem."