Welcome Guest
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
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

only a simple function :)

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

zip format
excel format

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

regards
sysp34

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

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.

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.

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

Good luck !

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

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)

takeemtothebank

Heroic City
Indonesia
Member #31689
February 2, 2006
1153 Posts
Offline
 Posted: February 5, 2006, 1:22 am - IP Logged

well the disadvantage  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)

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

regards
sysp34

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.

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

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) ?

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

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

the file .xls design for monthly analysis.

i don't have any idea to create formula with week, or month conditions as a filter  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

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

the file .xls design for monthly analysis.

i don't have any idea to create formula with week, or month conditions as a filter  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!!!

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"?

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.

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.

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.

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

 Page 1 of 2