Excel Help needed...formula

Reply #18

hypersoniq — Mon, 05 Jan 2004 02:34:44 GMT

thanks for the tip MikeK.I have 2 PCs, one with excel 2000 (office2k) and one with excel 2002 (officeXP), I used to have MOD formulas, but when bouncing them around between the 2 PCs over the home LAN, it always recalculates in XP if I modify it in 2k, and my solution, though less compact, seems to recalculate faster than when I used the MOD function.

Reply #17

MikeK — Mon, 05 Jan 2004 02:20:20 GMT

Hyper,Excel saves the formulas hardly packed in text format, as far as I know.For example your:F1 extracts the THIRD position number from the combo... =INT((C1-((D1*100)+E1*10)))could be cut down to =MOD(C1,10) with the same result and so on and so on..Good Luck with the Access approach.

Reply #16

hypersoniq — Sun, 04 Jan 2004 13:54:54 GMT

I just got finished importing all of my excel data into an MS access database, maybe I will have better luck there figuring out the counting of skips for use with backtesting...excel file is 36MB, access file is only 7 megs..

Reply #15

hypersoniq — Sun, 04 Jan 2004 12:30:43 GMT

glad it's of use luckylady... hopefully it helps rake you in some $$$ down the road...

Reply #14

luckylady — Sun, 04 Jan 2004 12:23:34 GMT

Thanks hypersoniq for the formula on extracting the combo # into their different positions. That will save me alot of typing

Reply #13

hypersoniq — Sun, 04 Jan 2004 11:48:19 GMT

wizards are those dialog boxes that help you complete certain tasks. THe Office assistant is that animated paper clip (there are a few other selectable avatars for it) that tries to guide you through using office. it helps as a search for help topics also.The office assistant isn't up on lottery lingo... when you need to recalculate 90,000 cells to update a 10 number rundown against a 9,000+ draw history then it tends to slow things up.

Reply #12

luckycat — Sun, 04 Jan 2004 08:45:41 GMT

whats a office wizard

Reply #11

interceptor — Sun, 04 Jan 2004 04:19:38 GMT

what happened to the office wizard or office assistant which comes with office? try enabling the wizard and it will guide you through .

Reply #10

MikeK — Sun, 04 Jan 2004 01:19:15 GMT

Sandy, Washington....Hyper, It is doable (at least indirectly) to count skips. I helped someone with it before. It mihgt require a few additional column for the numbers but those could of course be hidden.Good Luck

Reply #9

hypersoniq — Sun, 04 Jan 2004 01:11:16 GMT

Pennsylvania here.

Reply #8

Sandy K — Sun, 04 Jan 2004 00:55:31 GMT

Hypersoniq and Mike K,What states are you in

Reply #7

hypersoniq — Sat, 03 Jan 2004 22:56:31 GMT

I know the frustration of excel when you just draw a blank...here's my current project that I have NO idea how to start...I want to be able to automate the Tic-Tac-Toe overdue number system AND be able to back test it against the draw history over time... Given that overdue numbers tend to change. Here's my idea...1. Either a heck of a formula or a VB macro that can count which digit in each positon is out the longest, this would return one combo digit2. like above, but out the second-longest3.... [ More ]

Reply #6

hypersoniq — Sat, 03 Jan 2004 22:44:23 GMT

anytime Sandy KNow If only I could figure out how to convert some excel skillz into lottery $$$, I hope you have much success with that part of it ;-)P.S. Nice formula MikeK, I never thought about that approach. A big plus AND minus with excel is how many ways you can end up with the same result... always cool to see new and different approaches...

Reply #5

Sandy K — Sat, 03 Jan 2004 22:36:29 GMT

You are welcome, hypersoniq................thank you and thank you to Mike K............I finally got it also........wow.....you guys are good.Thank you................throwing a big kiss to you both.

Reply #4

hypersoniq — Sat, 03 Jan 2004 21:56:46 GMT

my pick 3 sheet has...A1=Draw Number (sequential from first drawing)B1=Draw DateC1=the combo (the only cell I need to actually enter when updating)D1 extracts the FIRST position number from the combo... =INT(C1/100)E1 extracts the SECOND position number from the combo... =INT((C1-(D1*100))/10)F1 extracts the THIRD position number from the combo... =INT((C1-((D1*100)+E1*10)))I have alot of other columns but among them is one for the sum... =SUM(D1:F1)Everything will autofill down when adding draw... [ More ]

Reply #3

hypersoniq — Sat, 03 Jan 2004 21:50:44 GMT

For the D1 sum ,if you are keeping separate columns by position then this is easiest... if you still want to keep the whole combo then put it in another column...if you have a drawing of 4-5-6... and in A1 have 4, in B1 have 5 and C1 have 6... then D1 is easy...=SUM(A1:C1

Reply #2

MikeK — Sat, 03 Jan 2004 21:19:14 GMT

Sandy,How about:=INT(D1*0.1)+D1-INT(D1*0.1)*10That would be what you wnat probably...This would work for 17 = 8 jiust fine, if you want 29 = 11 = 2 I woudl have to add something...Let me know...Good Luck

Reply #1

hypersoniq — Sat, 03 Jan 2004 21:18:46 GMT

if you have 17 or any other 2 digit number in your sum column, simply use LEFT and RIGHT... like this...IF your SUM is in D1, in E1 place the following formula...=IF(D1>10, (LEFT(D1)+RIGHT(D1)), D1)that's all there is to it, when D1 changes then so does E1, BUT if A1 is less than 10, it will only carry over the single digit, if you skip the IF, then entering a 6 in D1 results in 12 because 6 is on both LEFT and RIGHT... I initially missed that but caught it at the last minute before submitting t... [ More ]

Excel Help needed...formula

Sandy K — Sat, 03 Jan 2004 21:12:08 GMT

I have put my knitting needles down for the night and I am in some need for help.I think I need a substring formula.Here is what I need.I have the sum of 17 in Cell D1In Cell E1, I want the root sum of 17 which is 8. But of course the sum of 17 in cell D1 and the root sum in E1 would change with each draw.Now I think you need to do a substring on the 1 and the 7 but I keep getting a Name2 error code. I just can't seem to get the formula right.I would like one of you wonderful excel experts t... [ More ]