Welcome Guest
Log In | Register )
You last visited April 21, 2018, 5:14 am
All times shown are
Eastern Time (GMT-5:00)

# Formula help

Topic closed. 6 replies. Last post 8 years ago by frenchie.

 Page 1 of 1
PrintE-mailLink
Thread Starter
Los Angeles
United States
Member #75410
June 2, 2009
554 Posts
Offline
 Posted: August 16, 2010, 4:20 am - IP Logged

Hi everyone,I need help for 1 formula, I'm not able yet to transfer link so here I explain.

This excel file is to keep track of every number's skip ( for the 1000 numbers daily3 ).

And when a number come out ,it start counting from 1 again and up until it hit again and with an ( Index ) formula maybe in column G, I'll be able to see where each number break they cycle, simple chart but I like to see how the numbers move around and see where are they breaking point.

But my problem is that the formula that convert the 3 digit to the whole number does not do a good job because, the number is not reconise by my other formula ??? here is my display.

A3    and going down : Draw number.

B3    and going down draw date.

C3    first digit of CA daily3 going down.

D3    second digit.

E3     third digit.

F3     is the (3) digits combine together to create the whole number with formula :    =C3&D3&E3                             maybe not the right one for that ?

G       empty column for separation.

And then in :

H1     000

E1      001

J1       002

ETC....   up   to  999.

Then I have to put the number 1 starting cell : H3  all the way to : ALS3  .

Then in cell :

H4  I have this formula :              =IF(ISNA(MATCH(H\$1,\$F4,0)),H3+1,1)

drag it all the way to ALS4 and then all the way down to the last draw.

Here is my problem this formula starting in H4 do not reconise the number in F3 and won't do the job !!?  when I drag it down for all the numbers it does not start at 1 when the number has been hit ??

Can someone help to correct it please,          Thank you.

COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6313 Posts
Offline
 Posted: August 16, 2010, 6:45 am - IP Logged

Hi everyone,I need help for 1 formula, I'm not able yet to transfer link so here I explain.

This excel file is to keep track of every number's skip ( for the 1000 numbers daily3 ).

And when a number come out ,it start counting from 1 again and up until it hit again and with an ( Index ) formula maybe in column G, I'll be able to see where each number break they cycle, simple chart but I like to see how the numbers move around and see where are they breaking point.

But my problem is that the formula that convert the 3 digit to the whole number does not do a good job because, the number is not reconise by my other formula ??? here is my display.

A3    and going down : Draw number.

B3    and going down draw date.

C3    first digit of CA daily3 going down.

D3    second digit.

E3     third digit.

F3     is the (3) digits combine together to create the whole number with formula :    =C3&D3&E3                             maybe not the right one for that ?

G       empty column for separation.

And then in :

H1     000

E1      001

J1       002

ETC....   up   to  999.

Then I have to put the number 1 starting cell : H3  all the way to : ALS3  .

Then in cell :

H4  I have this formula :              =IF(ISNA(MATCH(H\$1,\$F4,0)),H3+1,1)

drag it all the way to ALS4 and then all the way down to the last draw.

Here is my problem this formula starting in H4 do not reconise the number in F3 and won't do the job !!?  when I drag it down for all the numbers it does not start at 1 when the number has been hit ??

Can someone help to correct it please,          Thank you.

Enter first formula in cell where you want current skip and drag down,

Ctrl-Shift-Enter =IF(COLUMNS(AQ19:AQ19)<=COUNTIF(\$AA\$18:\$AA\$836,\$AK19),SMALL(IF(\$AA\$18:\$AA\$836=\$AK19,ROW(\$AA\$18:\$AA\$836)-ROW(\$AA\$18)+1),COLUMNS(AQ19:AQ19))-1,"")

enter this formula in cell next to first formula and drag across and down

Ctrl-Shift-Enter  =IF(COLUMNS(\$AQ19:AR19)<=COUNTIF(\$AA\$18:\$AA\$836,\$AK19),SMALL(IF(\$AA\$18:\$AA\$836=\$AK19,ROW(\$AA\$18:\$AA\$836)-ROW(\$AA\$18)+1),COLUMNS(\$AQ19:AR19))-SUM(\$AQ19:AQ19)-1,"")

Pennsylvania
United States
Member #2218
September 1, 2003
5793 Posts
Offline
 Posted: August 16, 2010, 12:59 pm - IP Logged

Hi everyone,I need help for 1 formula, I'm not able yet to transfer link so here I explain.

This excel file is to keep track of every number's skip ( for the 1000 numbers daily3 ).

And when a number come out ,it start counting from 1 again and up until it hit again and with an ( Index ) formula maybe in column G, I'll be able to see where each number break they cycle, simple chart but I like to see how the numbers move around and see where are they breaking point.

But my problem is that the formula that convert the 3 digit to the whole number does not do a good job because, the number is not reconise by my other formula ??? here is my display.

A3    and going down : Draw number.

B3    and going down draw date.

C3    first digit of CA daily3 going down.

D3    second digit.

E3     third digit.

F3     is the (3) digits combine together to create the whole number with formula :    =C3&D3&E3                             maybe not the right one for that ?

G       empty column for separation.

And then in :

H1     000

E1      001

J1       002

ETC....   up   to  999.

Then I have to put the number 1 starting cell : H3  all the way to : ALS3  .

Then in cell :

H4  I have this formula :              =IF(ISNA(MATCH(H\$1,\$F4,0)),H3+1,1)

drag it all the way to ALS4 and then all the way down to the last draw.

Here is my problem this formula starting in H4 do not reconise the number in F3 and won't do the job !!?  when I drag it down for all the numbers it does not start at 1 when the number has been hit ??

Can someone help to correct it please,          Thank you.

Frenchie,

Here is a download link to zip file that contains California Pick 3 Draws.

The 1st tab is where you click the specific toolbar to add additional draws.  Midday, Evening, and Combined.  All you do is just click the Toolbar and go to the Tab M = Midday, E = Evening, or C = Combined then enter the new drawing.  All draw history has been updated through Sunday August 15, 2010.

There 3 additional Tabs for Midday, Evening, and Combined that will give you the current hits and skips for all 1000 Pick 3 Combinations.

Hope this helps!!

Here is the download link:

http://www.box.net/shared/pbv6g230hb

Thread Starter
Los Angeles
United States
Member #75410
June 2, 2009
554 Posts
Offline
 Posted: August 16, 2010, 8:35 pm - IP Logged

CARBOB,

Thank you very much for your quick respond, but it doesn't fit my excel file that I explain in the fisrt post!!!

or I'm the one who don't understand ? I just need a formula for my column F.

What I do is that, I download the draw history from CA daily3 evening website which is in " Text Document " and then tranfer it into my excel spread sheet, and I think that's where the problem is?

when it is transfer into excel it doesn't tranfer as number but under something else, and I went to the " Number Format " window to change it but nothing works !!! It ONLY work when I type the number over with my keybord.

So what I need is to have a formula that will tranfer those 3 digits into a number as a number,I think that what it is ?

Thank you CARBOB...

Thread Starter
Los Angeles
United States
Member #75410
June 2, 2009
554 Posts
Offline
 Posted: August 16, 2010, 10:57 pm - IP Logged

winsumloosesum,

Thank you very much for your file it's very interesting.

Question : can this same file be done for French Keno 20/70 ?          Thank you.

COLUMBUS,GA.
United States
Member #4924
June 3, 2004
6313 Posts
Offline
 Posted: August 17, 2010, 4:36 am - IP Logged

type a 1 in an empty cell, copy that cell, goto F3, paste special, multiply. Then format col F , Custom, type in 000. See if that works.

Thread Starter
Los Angeles
United States
Member #75410
June 2, 2009
554 Posts
Offline
 Posted: August 17, 2010, 6:35 am - IP Logged

CARBOB,

Thank you ,it works I will keep that trick in mind for the next time,Thank you for your help,I realy appreciate it.

Regards...                         Serge.

 Page 1 of 1