Excel FYI

Reply #33

hypersoniq — Mon, 12 Dec 2005 04:35:02 GMT

right now I am getting some ideas for the MASSIVE backtesting sheet that will be required. Also,using conditional formatting and a function called LARGE to show the top 2 numbers to play.works like this=LARGE((D1:K1),1) 'this returns the same as MAX in a range (Largest Number)=LARGE((D1:K1),2) 'this returns the NEXT largest number in the rangeso putting formula 1 in M1 and formula 2 in N1, then creating a conditional format on the entire value array D1:Kwhatever... Spotting the top 2 is no... [ More ]

Reply #32

retxx — Mon, 12 Dec 2005 04:16:10 GMT

will look forward to you releasing it. need something positive for Mass. pic 3/4

Reply #31

hypersoniq — Mon, 12 Dec 2005 03:39:07 GMT

that was a demo (lotto specific) for dynamic named ranges.I have sheets (parts of which were just developed hours ago) for the PA pick3(eve only) and PA pick4(eve only). but they are for a specific system still in development (more on that in my blog). These are not ready for distribution yet (LOOOONG recalculation time when adding a draw, and NOT as easy as the v-trac sheets to customize for different states)I ran a test with the pick3 in pa tonight and caught the back pair in 8 combos, since t... [ More ]

Reply #30

retxx — Mon, 12 Dec 2005 01:57:05 GMT

hyper i downloaded from your link. my question is I noticed the chart was for a lotto game correct. I was looking for a pick 3/4 chart.Did i download the wrong one? thanks

Reply #29

hypersoniq — Sun, 11 Dec 2005 19:02:57 GMT

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-9that 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 pairI have 3 worksheets like this, on... [ More ]

Reply #28

hareless — Sun, 11 Dec 2005 04:02:01 GMT

nope, didn't post right...sigh

Reply #27

hareless — Sun, 11 Dec 2005 03:01:27 GMT

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/l11/21/05581581588151H 11/22/054004044 0L11/23/05198198199818

Reply #26

hypersoniq — Sun, 11 Dec 2005 02:06:42 GMT

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.read about it here... http://www.beyondtechnology.com/geeks007.shtmlI 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 tipIf you have calculation... [ More ]

Reply #25

hypersoniq — Sat, 10 Dec 2005 19:40:47 GMT

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 au... [ More ]

Reply #24

powerplayer — Sat, 10 Dec 2005 15:01:46 GMT

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

Reply #23

lastexit51 — Sat, 10 Dec 2005 13:58:09 GMT

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.

Reply #22

lottaloot — Sat, 10 Dec 2005 13:15:46 GMT

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.

Reply #21

lottaloot — Sat, 10 Dec 2005 13:09:11 GMT

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

Reply #20

retxx — Sat, 10 Dec 2005 03:25:04 GMT

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

Reply #19

cps10 — Fri, 09 Dec 2005 16:34:31 GMT

Thanks winsumloosesum I will try that one out.

Reply #18

winsumloosesum — Fri, 09 Dec 2005 16:26:35 GMT

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 = DateB10 = Pick 3 Number DrawnC10 = Position 1D10 = Position 2E10 = Position 3My worksheet name is PA3MI have a second worksheet name called: ChartsIt has a chart like below:The P1 is in cell B1, P2 is in cell C1 etcThe 0 is in cell A2, 1 in A3 etc. P1P2P30 1 2 3 4 5 6 7 8 9 So in cell B2 I have the folowing formula:=MATCH

Reply #17

cps10 — Fri, 09 Dec 2005 15:47:52 GMT

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

Reply #16

winsumloosesum — Fri, 09 Dec 2005 15:37:11 GMT

lottaloot,This might help.If you create a worksheet and name it SumsList all the Sums from 0 through 27 in column AList all the Roots in column BA B0 01 12 23 34 45 56 67 78 89 910 111 212 313 414 515 616 717 818 919 020 221 322 423 524 625 726 827 9The worksheet with all your drawings.My worksheet has the total sums in cell column VMy root sum is in column W with... [ More ]

Reply #15

lottaloot — Fri, 09 Dec 2005 04:50:46 GMT

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.

Reply #14

JKING — Fri, 18 Nov 2005 13:32:10 GMT

Carbob...Thanks. A well written and informative answer.

Reply #13

CARBOB — Thu, 17 Nov 2005 16:02:05 GMT

JKing, Below is your answer. I asked the old reliable boys at Mrexcel.com, since I didn't know either. Now we both will have learned something.Carbob ANSWER........It's called a double unary minus. Minus minus is the same as plus.It coerces a TRUE/FALSE boolean into 1/0. The same could be achievedby multiplying by 1 or adding zero. But -- is in vogue at the moment becauseapparently it is a nanosecond or two quicker than the alternatives.It would more likely be written:=SUMPRODUCT(($B$2:$B$8... [ More ]

Reply #12

JKING — Wed, 16 Nov 2005 16:45:52 GMT

Is anyone familiar with what the -- is?=SUMPRODUCT(--($A$2:$A$9=G2),--($B$2:$B$9=H2

Reply #11

pacattack05 — Wed, 16 Nov 2005 14:32:36 GMT

Thanx Carob.

Reply #10

lottaloot — Wed, 16 Nov 2005 12:01:29 GMT

This is getting better better by the day.

Reply #9

CARBOB — Wed, 16 Nov 2005 10:58:19 GMT

For Your InformationCarbob

Reply #8

pacattack05 — Wed, 16 Nov 2005 07:20:33 GMT

What does FYI stand for thanx.

Reply #7

retxx — Wed, 16 Nov 2005 03:17:37 GMT

P.S. can you give an example to me how you would do this formula as I would like to apply it to the pick 4 in Mass.

Reply #6

retxx — Wed, 16 Nov 2005 03:15:43 GMT

question; in a pick 4 lottery if I entered the first 2 in column L1 and the last 2 in column L2 and did it as far back as the last 10 or 20 draws and entered your formula would i get the same resu lts to see what overdue pairs to play? thanks

Reply #5

CARBOB — Wed, 16 Nov 2005 00:43:36 GMT

JKing, Your formula with a slight variation. I use the formula below to tell me how many times a digit has followed another digit. The first part of the equation going across, the second part down. Winsum and Sir Metro have both helped me a great deal. Many thanks to them.Carbob =SUMPRODUCT(--($G$21:$G$3062=BS$21),(--($G$22:$G$3063=$BR22))) 1234511101241211301192127111118124114312512811212114041211111471071125120121128116135

Reply #4

bellyache — Mon, 14 Nov 2005 05:33:07 GMT

That's interesting JKing. Thanks.

Reply #3

LottoChica23 — Mon, 14 Nov 2005 05:15:52 GMT

Thanks for sharing, this formula is very useful

Reply #2

lottaloot — Sun, 13 Nov 2005 19:34:10 GMT

It works wonderfully! Just incorporated it into my OK pick 3 sheet.If you have any other excel FYI--please know that I would appreciate your posts.

Reply #1

lottaloot — Sun, 13 Nov 2005 19:26:49 GMT

Thanks for sharingI am like a sponge when it comes to new excel formulasI can find a use for any formula. going to try inserting it into my spreadsheet now.

Excel FYI

JKING — Sun, 13 Nov 2005 14:16:11 GMT

If your like me, you create an excel spread sheet, input the data, sort the data in ascending order and then perform various functions on that data. In the example below, I wanted to know how often a certain value in L1 occurred with a certain value in L2. In this case, how many times did 1-(L1) and 2-(L2) occur together. The answer is as follows:=SUMPRODUCT(--($A$2:$A$9=G2),--($B$2:$B$9=H2)) Microsoft Excel a b c d e f g h i 1 L1 L2 L3 L4 L5 COL L1 COL L2 NO. of Times 2 2 2 2 3 7 1 2 3 3 1... [ More ]