Excel Users - Pick 3 & Pick 4

Reply #23

time*treat — Thu, 15 Dec 2005 09:53:03 GMT

A macro will let you interact across multiple worksheets and across multiple workbooks. Each has it's advantages.It looks like you want to track the number first, and the state second.You could set the states next to each other by column, or put each one on a seperate sheet, making sure that e.g. Dec. 11 is on the same row in each sheet. Then, when you do a row comparison, you would add an extra loop that would let you check that row for each state.Sort of saying:check row 500 of every sheet and... [ More ]

Reply #22

powerplayer — Thu, 15 Dec 2005 09:15:55 GMT

Thank you time*treat WSLSI belive this will help. Going to work on it tonight.I want to do more then just one state also. I want to track the movement of a combination through each state.Example # 120:Tue, Dec 13, 2005KentuckyPick 31-2-0Mon, Dec 12, 2005OhioMidday Pick 31-0-2Sun, Dec 11, 2005CaliforniaMidday 30-1-2Sun, Dec 11, 2005Washington, D.C.Midday Lucky Numbers0-2-1This # has come out 4 times since Dec. 11th.I want the formula to do a hit and skip tracking from washington to cali to ohio... [ More ]

Reply #21

time*treat — Thu, 15 Dec 2005 04:52:01 GMT

for powerplayer,if you're just looking for the number of skips between every instance of e.g. 123 in one state, it's basically an if-then inside a for-next loop inside a do-while(while-wend). If 2 numbers have the same lowform value, the skip is (draw A - draw B - 1). Set draw B as your new start point (new draw A) and look for the next instance of lowform. Do that until you run out of numbers. You'll just need a few vars to track the positions of A and B, but you have all the parts here. If you... [ More ]

Reply #20

lottaloot — Thu, 15 Dec 2005 01:00:09 GMT

I am designing my (all states) spreadsheet to look like this. All on 1 page.In the end, I hope to figure out formulas that will apply a conditional format for all the filters that Lantern has taught in his tutorials. DateCA3H/LE/OROOTSUMLDR12/12Mon 966HHHOEE321112/13Tues 683HHLEEO817712/14Wed 219LLHEOO3122

Reply #19

lottaloot — Thu, 15 Dec 2005 00:55:50 GMT

That's shorter than the one I attempted to make. I sure wish I knew how to make short formulas--but I guess it is going to come with time.

Reply #18

lottaloot — Thu, 15 Dec 2005 00:52:27 GMT

Thanks Carbob

Reply #17

CARBOB — Wed, 14 Dec 2005 21:55:47 GMT

LL,=SUM((MID(A3,{1,2,3},1)+0

Reply #16

lottaloot — Wed, 14 Dec 2005 21:39:48 GMT

Thanks WSLS

Reply #15

winsumloosesum — Wed, 14 Dec 2005 21:19:35 GMT

Code also good for Pick 3, Pick 4, Pick 5, Pick 6, Pick 7, Pick 8, Pick 9, Pick 10 LOL

Reply #14

winsumloosesum — Wed, 14 Dec 2005 21:03:41 GMT

Kim,http://www.exceltip.com/st/Sum_the_digits_numbers_in_a_cell_using_custom_VBA_function_in_Microsoft_Excel/620.html

Reply #13

lottaloot — Wed, 14 Dec 2005 20:41:07 GMT

Q. Does anyone know of a formula that will give the sum of a single cell for a pick 3 combo? or do they always have to be in separate cells in order to return the sum??Instead of 789 where C2=(7), C3=(8), C4=(9)Have C1=(789) and then give the sum=(24) in C2Is there any other way around the separate cells

Reply #12

powerplayer — Wed, 14 Dec 2005 11:39:20 GMT

I wondering if any one has a excel formula that can do a skip and hit but, by a full combination (123)?I want to write a formula or vb script that will go through all my states on my excel sheet and count how many skips inbetween for each hit of a combination I specify from state to state.A push in the right direction would help also.Any help would be great

Reply #11

hypersoniq — Sat, 10 Dec 2005 19:50:50 GMT

here is a good place to see excel in action via tutorial examples...http://www.exceltip.com/covers macros and vba alsoand here is a macro-specific one...http://computerwhizzard.50megs.com/excelminitutorial.htmllottaloot: you don't need to exit excel to learn how to write and edit macros... It's all in the excel help files.

Reply #10

lottaloot — Sat, 10 Dec 2005 16:30:17 GMT

I appreciate you sharing this with the forum.I would love to know how to write macros. Where did you learn to write them? any books that you can refer? I was thinking of taking a class on excel somethime in the future.

Reply #9

time*treat — Sat, 10 Dec 2005 15:06:20 GMT

You're welcome LC23.It's nice to know my 1's and 0's are being acknowleged by someone.I should also mention the above formula/function works with references, too (those R1C1, $A$1 thingies). I prefer functions to cell-by-cell formulas mainly for 2 reasons.1. the file size is MUCH smaller, if you use 1000's of cells.2. if you decide you want to change things, you do so in one place, one time. When the sheet is recalculated, everything is updated.

Reply #8

LottoChica23 — Sat, 10 Dec 2005 01:22:40 GMT

Thank You

Reply #7

time*treat — Sat, 10 Dec 2005 01:07:17 GMT

My formulas tend toward verbosity...play with this and tell me how you like it... <-- maybe I should re-word this create a macro/module page in XL c p this ...'-------------------------------' Function lowform_3(three_digit) Dim channel() As Integer Dim slot() As Integer Dim num As Integer, pos As Integer Dim c0, c1, c2, c3 c0 = three_digit c1 = Int(c0 / 100) c2 = Int((c0 Mod 100) / 10) c3 = c0 Mod 10 ReDim channel(9) ReDim sl... [ More ]

Reply #6

lottaloot — Sat, 10 Dec 2005 00:33:09 GMT

It's not that difficult. Just Left click of the mouse pull down the formula. I got no problem with it but if you could shorten it, I am sure it would be most appreciated.

Reply #5

time*treat — Sat, 10 Dec 2005 00:28:45 GMT

I'd hate to have to cut paste THAT one a couple hundred times. Talk about parenthese purgatory. I have actually used longer versions of code when I thought it would make things easier to follow/edit later. Maybe we could all create a clearinghouse for our formulas and macros.

Reply #4

lottaloot — Fri, 09 Dec 2005 23:48:33 GMT

This formula will also convert the pick 3 into it's box form. I am sure that it could be shortened but I don't know how to do it. =CONCATENATE((MIN(LEFT($C2),MID($C2,2,1),RIGHT($C2))),(MEDIAN(LEFT($C2),MID($C2,2,1),RIGHT($C2))),(MAX(LEFT($C2),MID($C2,2,1),RIGHT($C2

Reply #3

time*treat — Fri, 09 Dec 2005 23:19:33 GMT

nice little link. I guess you folks haven't gotten into writing your own functions yet? Then there is no need to provide extra instructions steps when you give away a file. When you share your eXcel file, the functions macros go along for the ride.

Reply #2

winsumloosesum — Fri, 09 Dec 2005 16:29:23 GMT

I should add this. If you give away your excel file with this formula you might want to tell the person your giving this file to, to download this add-in also. Or it won't work.

Reply #1

cps10 — Fri, 09 Dec 2005 16:07:18 GMT

Thanks winsumloosesum - that is very interesting.

Excel Users - Pick 3 & Pick 4

winsumloosesum — Fri, 09 Dec 2005 16:04:00 GMT

For those who use Excel. Wanted to share this formula for those interested in converting a Pick 3 and Pick 4 drawn number to its ascending form.Example: Pick 4 4 3 9 0 = 0 3 4 9 Pick 3 6 1 3 = 1 3 6The first thing you will need is to download the FREE Add-in form the folowing link:http://xcell05.free.fr/english/Scroll down the page until you see MOREFUNC.XLLDownload, unzip and install.Open Excel>Tools>AddIns>select Morefunc in the list and select OKGo to the cell you want t... [ More ]