Welcome Guest
You last visited December 4, 2016, 9:25 pm
All times shown are
Eastern Time (GMT-5:00)

# Excel FYI

Topic closed. 33 replies. Last post 11 years ago by hypersoniq.

 Page 2 of 3
Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: December 8, 2005, 11:50 pm - IP Logged

Here's a helpful formula for changing sums into root form.  I figured this one out all by my little self.

=IF(AB2>9,RIGHT(AB2,1)+LEFT(AB2,1),AB2)

It works well except for the sum of 19 (it changes that into 10 instead of 1)  Can't figure that one out yet.

I am pretty sure that there should be an (or) function in there somewhere.

L ttaL   T

Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
 Posted: December 9, 2005, 10:37 am - IP Logged

lottaloot,

This might help.

If you create a worksheet and name it Sums

List all the Sums from 0 through 27 in column A
List all the Roots in column B

A             B

0             0
1     1
2     2
3     3
4     4
5     5
6     6
7     7
8     8
9     9
10     1
11     2
12     3
13     4
14     5
15     6
16     7
17     8
18     9
19     0
20     2
21     3
22     4
23     5
24     6
25     7
26     8
27     9

The worksheet with all your drawings.

My worksheet has the total sums in cell column V

My root sum is in column W with this formula: =LOOKUP(V2,Sums!\$A\$1:\$A\$28,Sums!\$B\$1:\$B\$28)

So V2 is where I have my most recent entry.

You can also add the other type of Sum (27 = 7) in the Sums worksheet.

0     0
1     1
2     2
3     3
4     4
5     5
6     6
7     7
8     8
9     9
10     0
11     1
12     2
13     3
14     4
15     5
16     6
17     7
18     8
19     9
20     0
21     1
22     2
23     3
24     4
25     5
26     6
27     7

The Carolinas - Charlotte
United States
Member #21627
September 12, 2005
4138 Posts
Offline
 Posted: December 9, 2005, 10:47 am - IP Logged

This was one I tried about 3 months ago and was curious about: how to find out the number of draws out with one particular number. For example, if 134 drew on 6/7/05 and then the 1 didn't draw again until 6/16/05, that would be 9 draws the 1 was out. Any formula for that?

Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
 Posted: December 9, 2005, 11:26 am - IP Logged

CPS,

My Excel drawings are in descending order.  Meaning, I have the most recent drawing at the top.  So my most recent drawing is on row 10.

A10 = Date

B10 = Pick 3 Number Drawn

C10 = Position 1

D10 = Position 2

E10 = Position 3

My worksheet name is PA3M

I have a second worksheet name called: Charts

It has a chart like below:

The "P1" is in cell B1, "P2" is in cell C1 etc

The "0" is in cell A2, "1" in A3 etc.

 P1 P2 P3 0 1 2 3 4 5 6 7 8 9

So in cell B2 I have the folowing formula:

=MATCH(\$A2,INDIRECT("PA3M!c10:c100"),0)-1

So what this formula is doing is matching the "0" in cell A2 and then going to the worksheet "PA3M" in cell range C10 through C100 and finding how many drawings it's been since the "0" Zero hit in Position 1.

So if in my most recent drawnig in Position 1 a "0" was drawn, a 0 would be in cell B2.

The Carolinas - Charlotte
United States
Member #21627
September 12, 2005
4138 Posts
Offline
 Posted: December 9, 2005, 11:34 am - IP Logged

Thanks winsumloosesum I will try that one out.

BOSTON
United States
Member #48
September 9, 2001
3584 Posts
Offline
 Posted: December 9, 2005, 10:25 pm - IP Logged

winsum could you post a link get the excel chart and will it show the past due pairs to play that are due? thanks

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: December 10, 2005, 8:09 am - IP Logged

lottaloot,

This might help.

If you create a worksheet and name it Sums

List all the Sums from 0 through 27 in column A
List all the Roots in column B

A             B

0             0
1     1
2     2
3     3
4     4
5     5
6     6
7     7
8     8
9     9
10     1
11     2
12     3
13     4
14     5
15     6
16     7
17     8
18     9
19     0
20     2
21     3
22     4
23     5
24     6
25     7
26     8
27     9

The worksheet with all your drawings.

My worksheet has the total sums in cell column V

My root sum is in column W with this formula: =LOOKUP(V2,Sums!\$A\$1:\$A\$28,Sums!\$B\$1:\$B\$28)

So V2 is where I have my most recent entry.

You can also add the other type of Sum (27 = 7) in the Sums worksheet.

0     0
1     1
2     2
3     3
4     4
5     5
6     6
7     7
8     8
9     9
10     0
11     1
12     2
13     3
14     4
15     5
16     6
17     7
18     8
19     9
20     0
21     1
22     2
23     3
24     4
25     5
26     6
27     7

Thanks for the above suggestion.

WSLS, for my LDR, I have the following formula in column BN.  Column BM has my sums listed.

=RIGHT(BM2,1)*1

L ttaL   T

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: December 10, 2005, 8:15 am - IP Logged

CPS,

My Excel drawings are in descending order.  Meaning, I have the most recent drawing at the top.  So my most recent drawing is on row 10.

A10 = Date

B10 = Pick 3 Number Drawn

C10 = Position 1

D10 = Position 2

E10 = Position 3

My worksheet name is PA3M

I have a second worksheet name called: Charts

It has a chart like below:

The "P1" is in cell B1, "P2" is in cell C1 etc

The "0" is in cell A2, "1" in A3 etc.

 P1 P2 P3 0 1 2 3 4 5 6 7 8 9

So in cell B2 I have the folowing formula:

=MATCH(\$A2,INDIRECT("PA3M!c10:c100"),0)-1

So what this formula is doing is matching the "0" in cell A2 and then going to the worksheet "PA3M" in cell range C10 through C100 and finding how many drawings it's been since the "0" Zero hit in Position 1.

So if in my most recent drawnig in Position 1 a "0" was drawn, a 0 would be in cell B2.

CPS10, I believe that you could you also change this formula to where it would search for the zero in any position using this formula.

=MATCH(\$A2,INDIRECT("PA3M!c10:e100"),0)-1

I am also getting the feeling that you can do more searching with less formulas if you add names to your sheet.

L ttaL   T

New Member
tn
United States
Member #19631
August 3, 2005
16 Posts
Offline
 Posted: December 10, 2005, 8:58 am - IP Logged

Is anybody running excel on a celeron processor? I want to get it but I read on the package that you have to have a pentium processor. The guy who fixed my computer said he thoght it would work but i would like another opinion.

United States
Member #17834
June 28, 2005
2083 Posts
Offline
 Posted: December 10, 2005, 10:01 am - IP Logged

Is anybody running excel on a celeron processor? I want to get it but I read on the package that you have to have a pentium processor. The guy who fixed my computer said he thoght it would work but i would like another opinion.

HI lastexit51,

Yes it's fine to run it on a celeron processor.

They are made by Intel but, it's a cheaper model then the real pentium.

Powerplayer

Good luck to everyone!!!

Pennsylvania
United States
Member #1340
April 6, 2003
2450 Posts
Offline
 Posted: December 10, 2005, 2:40 pm - IP Logged

I like the SUMPRODUCT formula.

Another great timesaver (and one that aids immensely in future readability) is the use of 'Named Ranges'... replace all of that \$A\$2:\$A\$9 with something like "number_1".

here is one from my latest system project...

=SUMPRODUCT(--(number_1=\$A2),--(announcer_1=B\$1))

this creates a grid where column A has numbers drawn (0-9) (A2=0, A11=9)

Row 1 of columns B:K are 0-9 (announcer numbers)

the grid located in B2:K11 contains the above formula (above is in B2) and was simply autofilled to create the grid

number_1 is off of another worksheet (see how easy named ranges are?) and represents the first digit drawn in the ENTIRE pa lottery evening game history (all 9,864 draws from 3/1/1977 to 12/9/2005)

announcer_1 is simply the numbers column copied and pasted to a new column one row lower (so I can see what number followed every number)

on the announcer worksheet I have 3 such grids, one for each position.

precursor_1 is the number drawn BEFORE the draw in question (same draw history, shifted a row UP), and that is the basis for this newest system, qualifying the announcer by frequency of the precursor, in each position... more to come on that later...

I also like the --, that's one I didn't know about before now.

thanks JKING for the formula and thanks CARBOB for the 411 on --

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

Pennsylvania
United States
Member #1340
April 6, 2003
2450 Posts
Offline
 Posted: December 10, 2005, 9:06 pm - IP Logged

Also, to up the info ante...

you can define what are called "Dynamic Named Ranges",or ranges that grow to accomodate new draws without re-typing or editing the formulas.

I use that in my pick3 sheet for all of PA's data, and the newly created pick4 sheet. it looks like this (entered in the "Refers to:" box when defining the named range)...

=OFFSET(sbdraws!\$D\$1,3,0,(COUNTA(sbdraws!\$D:\$D)-2),1)

another quick tip

If you have calculations, such as

1. separating combos into draws

2. converting to v-tracs or other filters

3. any other operations that use formulas that output static numbers

select the range where all of the formulas are (except for the most recent row, so you don't lose your formulas)...click COPY... then go to edit-paste special... then choose VALUES. this replaces the autofilled formulas with the numeric results.

what good is that, you ask?

before doing that, my pick 3 file was 3.45MB, after doing that, 1.3MB. it loads faster and recalculates quicker too.

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

Anna, TX
United States
Member #26720
November 21, 2005
263 Posts
Offline
 Posted: December 10, 2005, 10:01 pm - IP Logged

hey I have a question. I am working on my high/ low filters..

I have a seperate sheet dedicated for it..anyway...

a                        b                c        d        e          f              g              h              i                j                 and so on...

date                  pick3            an1  an2    an3      lth            rth            side          h/l            h/l              h/l

 11/21/05 581 5 8 1 58 81 51 H 11/22/05 4 0 0 4 0 4 4 0 L 11/23/05 198 1 9 8 19 98 18 1 L 11/24/05 929 9 2 9 92 29 99 2 L 11/25/05 394 3 9 4 39 94 34 3 L 11/26/05 752 7 5 2 75 52 72 4 L 11/27/05 na 5 H 11/28/05 842 8 4 2 84 42 82 6 H 11/29/05 426 4 2 6 42 26 46 7 H 11/30/05 345 3 4 5 34 45 35 8 H 12/01/05 na 9 H 12/01/05 94 0 9 4 9 94 4 12/02/05 874 8 7 4 87 74 84 12/03/05 623 6 2 3 62 23 63 12/05/05 468 4 6 8 46 68 48 12/06/05 126 1 2 6 12 26 16 12/07/05 219 2 1 9 21 19 29 12/08/05 878 8 7 8 87 78 88 12/09/05 786 7 8 6 78 86 76

formulas in lth, rth and side have this formula (well changes for each cell, you know what I mean)

=INT((D3+(C3*100/10)))

now I want the h/l rows to display either hh, hL, Lh, LL and not just one H or Lwhich is all I have been able to do so far...

I hope this displays right and I made sense. I can forward my spreadsheet if you want to get a better understadning

Anna, TX
United States
Member #26720
November 21, 2005
263 Posts
Offline
 Posted: December 10, 2005, 11:02 pm - IP Logged

nope, didn't post right...sigh

Pennsylvania
United States
Member #1340
April 6, 2003
2450 Posts
Offline
 Posted: December 11, 2005, 2:02 pm - IP Logged

here is my latest evolution of this formula, inserting a logical combination of a draw with the draw before it, then scaning the history to count the announcer frequency...

=SUMPRODUCT(--((precursor_3*10)+number_3=\$A2),--(announcer_3=B\$1))

where column A is a list of combinations from 00 to 99, row 1 of columns B:K are 0-9

that formula tells me a full count (for one position) of how many times the numbers 0-9 appeared as announcers with any given precursor+draw pair

I have 3 worksheets like this, one for each position.

I can use it like this...

if last night's first position number was 4 and the night before was 8, this becomes 84, all I have to do is scroll down to 84 in column A to see the distribution of ALL the announcer digits

I can see that the most frequent follower of 84 is 0, which happened 16 times. pure announcer data (without precursor info) shows 3 as the most frequent follower of 4 in position one.

now all I have to do is figure out the next step of the "system"

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

 Page 2 of 3