  # need excel guru & master 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 WOW!  (says the Lootster with eyes big & glazed over) At worst, someone is asking for a whole bunch of gratis effort.

At best, I am not understanding this post. 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... 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 !  WOW!  (says the Lootster with eyes big & glazed over)  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 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. Also, by the way, it is a little hard to understand you, which other language do you speak besides English (If any other) ? 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 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 At worst, someone is asking for a whole bunch of gratis effort.

At best, I am not understanding this post.

"gratis effort"? gratis = free, but I did say "at worst". 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. 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.

Welcome Guest

Your last visit: Tue, Oct 20, 2020, 11:55 pm