Excel Help

Reply #28

SergeM — Wed, 09 Jan 2013 13:54:34 GMT

I use Excel 2010.

I use Excel 2010.

Reply #27

phileight — Wed, 09 Jan 2013 13:51:41 GMT

what version of Excel are you using? this is not an option in 2007 version. it certainly would be easier

Reply #26

SergeM — Wed, 09 Jan 2013 13:32:12 GMT

The other way but I would use an array formula in a normal case.

Reply #25

phileight — Wed, 09 Jan 2013 10:51:30 GMT

I understand it well enough to use, but to explain it i'm not sure. I would not use this, I would write a vba routine to do it. for me much easier to understand

the mod function will reduce whatever you feed it to something less than what you specify as a divisor. in this case the divisor is 2 so the answer for each digit in your range is either 0 or 1. 0 its an even #, 1 its an odd #. sum product is an array function that counts how many times something occured that matches what you specify,... [ More ]

Reply #24

LottoBoner — Wed, 09 Jan 2013 00:36:27 GMT

Thanks phileight.

I put the proper range, and so far its working like a charm.

Now if I can only make sense of what you typed

Reply #23

phileight — Wed, 09 Jan 2013 00:03:29 GMT

in your example you did not specify what col or row your information was located. I had to assume a couple of values( b1:f1 ) if your data is in a different location then you need to change these references to match your data locations. the good news you only need to do it once. after that you can copy and paste

Reply #22

LottoBoner — Tue, 08 Jan 2013 23:32:59 GMT

Reply #21

SergeM — Tue, 08 Jan 2013 22:55:31 GMT

Array formula

Reply #20

LottoBoner — Mon, 07 Jan 2013 00:55:23 GMT

I put this formula and it brought back a value, I just am not sure if the value matches up

Do i have to put a range? Or can i just put one cell value

Reply #19

LottoBoner — Mon, 07 Jan 2013 00:49:01 GMT

My programmer provided these charts to me.

I use them daily in every game i play.

-1 0 0 -1 0 0 -4 -1 -1 -1

-2 -1 0 0 0 -1 -5 -2 0 0

-3 0 0 -1 0 -2 -6 -3 0 -1

-4 0 0 0 -1 -3 0 -4 0 -2

-5 -1 0 0 -2 0 0 -5 0 -3

-6 0 -1 0 -3 -1 -1 0 0 -4

-7 -1 0 0 -4 0 0 0 -1 -5

-8 -2 0 -1 0 -1 0 0 -2 0

-9 0 0 -2 -1 0 0 -1 -3 -1

-10 -1 -1 -3 -2 0 0 0 -4 -2

-11 -2 -2 -4 0 -1 0 -1 -5 0

0 -3 -3 0 -1 -2 0 -2 -6 0

0 0 -4 -1 0 -3 0 -3 -7 -1

0 0 -5 0

Reply #18

LottoBoner — Mon, 07 Jan 2013 00:45:40 GMT

Yeah my charts are spot on, it just I have to do some fine tuning. Its one of my new years resolutions.

I use a lot of countifs.

There was a post that I made as a favorite, giving the answer to a question somebody posed. Cant seem to find it though!

But I found a lot of interesting excel stuff and weird vba stuff.

For me i just fill in cells, then the vba is done automatically, but you goes are doing the reverse.

There are some cool objects that I use that you can make transparen... [ More ]

Reply #17

phileight — Mon, 07 Jan 2013 00:30:46 GMT

I'm glad you posed this question. I've learned something today. you in fact can combine text and formulas in the same cell. in the cell you want your results,

= E sumproduct((mod(b1:f1,2)=0)+0) O sumproduct((mod(b1:f1,2) 0)+0)

b1:f1 is your data range adjust as needed.

Reply #16

LottoBoner — Sun, 06 Jan 2013 21:17:38 GMT

Reply #15

omiller315 — Sun, 06 Jan 2013 21:00:41 GMT

It can be pretty involved. I have a college degree in programming and one of the things I learned was VB6, so its pretty easy to move it to VBA. Def read up the book and learn what you can. VBA makes Excel way more powerful that Excel already is.

Reply #14

LottoBoner — Sun, 06 Jan 2013 20:15:08 GMT

Thanks everybody for posting.

The VBA is a little light years ahead of me, so i started reading that book that somebody posted in learning VBA.

The author says the meat and potatoes of the VBA is in chapter 11.

The book has 900 pages, and I only just started reading and am in chapter 1, page 45, so I will see how this develops.

Right now my non VBA formula is analyzing one cell that counts the even occurences.

So my current formula is,

Well I started with this and then I got st... [ More ]

Reply #13

dr san — Sun, 06 Jan 2013 16:00:04 GMT

Hello, omiller, heels, are the distances between the digits example

The digit 8, the base is 1 draw, the digit 8 has once again given after 5 draws

After 12 then draws 3 .... etc then jump by position or column

The eighth digit is = 5,8,3 ... etc, may further 0-10 low heel 10-20 heeled

The jump is to see each upright position, and not by any of the three digits

Sure will have to do statistics for each position in the vertical pick3

Reply #12

omiller315 — Sun, 06 Jan 2013 14:53:18 GMT

What do you mean by jumps? Below is for high low

Yeah. Would just add

If Cells(1,1) 5 then

low = low +1

else

high = high +1

I like to use counters and loops and would look like this.

Sub highligh()

Dim high As Long, low As Long

Dim row As Long, column As Long

row = 1: column = 1 '(if you have a header row change to 2 or if your #'s start in column 2, then change column to 2

Do Until Cells(row, 1) = ' this is the main loop to run until it doesn't see any... [ More ]

Reply #11

phileight — Sun, 06 Jan 2013 11:02:44 GMT

to get exactly what you asked for would require some VBA. If output in two columns would be acceptable you can combine sumproduct and mod functions to count even and odd. if we assume that the 6 is cell b1 and the 34 is cell f1 and the question mark is cell k1 and it will be count of even # l1 will be count of odd# the formula in k1 would be:

=sumproduct((mod(b1:f1,2)=0)+0) count even # adjust b1:f1 to suit your range

in l1

=sumproduct((mod(b1:f1,2) 0)+0 count odd # adjust as above

c... [ More ]

Reply #10

dr san — Sun, 06 Jan 2013 10:21:08 GMT

Hello, omiller, can also add, high / low = 0,1,2,3,4 low

High = 5,6,7,8,9

And also jumps

Reply #9

omiller315 — Sat, 05 Jan 2013 22:50:33 GMT

Here is the VBA code for odds/even assuming we are talking about entries in A1 and result in B1. To do multiple rows, we would just implement it into a DO loop with variables til it reached the end and factor in multiple columns. Will gladly help if you want. Honestly shouldnt be more than about 10-20 lines of code.

Forgot to add the counter. This would then allow you to post the odd/even results at the end of the row. We can go 1 step further and concatenate it to a 2E/3O like you mentioned... [ More ]

Reply #8

LottoBoner — Sat, 05 Jan 2013 22:31:49 GMT

Thanks omiller.

Well the spacing is easy. just (CELLB - CellA )-1

I am not confident in VBA yet. My trouble is that my sheet is already a mess, then when i go to VBA code i am like

If i was to build from bottom up, then I may be able to catch on.

I do need to build from bottom up again anyway, so I can prioritize my paramaters and variables.

Yes always five numbers for Pick5, but i also play pick6 so same problem

Reply #7

omiller315 — Sat, 05 Jan 2013 22:22:51 GMT

Most likely going to have to be done in VBA script and should be pretty simple. I can write the code if you feel confident implementing it. Will it always be 5 #'s and do you want the VBA code to also figure out the spaces between each #?. Do you also want the odd/even of just the lotto #'s or the spaces in between also

Reply #6

notmyday — Sat, 05 Jan 2013 21:26:34 GMT

7 goes in the question mark

Reply #5

SergeM — Sat, 05 Jan 2013 21:20:38 GMT

There are indeed several ways.Maybe you should do them all.

Reply #4

LottoBoner — Sat, 05 Jan 2013 20:33:28 GMT

Reply #3

Greenfox — Sat, 05 Jan 2013 20:15:30 GMT

They've sure helped me out a plenty.

It's a great idea. It shows it in a completely different way that's for sure. And yes, it does bring the 0 into effect. In more ways than just the 24 to 25 from what i just saw.

Very nice indeed

Reply #2

LottoBoner — Sat, 05 Jan 2013 20:07:20 GMT

Thanks Greenfox!

I will definitely check out that excel forum.

Yes the space in between helps, if you can focus on a spacing you want or one that you believe is due. Of course it doesn't really purely apply to pick 3 since there are no real spacings between numbers because of the three different machines.

Yeah I like spacing instead of difference because I love to include the almighty 0.

Reply #1

Greenfox — Sat, 05 Jan 2013 19:42:26 GMT

Hey LottoBoner,

I don't know how to do this, but love the idea. I think i see where your going with it.

For my stuff when i couldn't figure something out myself, i went to excelforum. The people there helped me everytime. And fast. One guy was even going to put a full day into my problem. I wouldn't let him though. But if you run into a wall with something, they are real nice there also.

But i really like this!!! Especially the spaces inbetween versus the addition or subtraction. Very n... [ More ]

Excel Help

LottoBoner — Sat, 05 Jan 2013 19:13:09 GMT

Ok this is kind of a beginners question, I have tried to tackle the problem, but with only moderate success.

I do have some other ideas, such as outputting to another sheet, then re-inputting, but I figured, If anybody can do it easier, then I would gladly like to cheat off them.

Basically where the question mark sign is I would like the output of odd/even in the format of

3O/2E, 2O/3E, etc.

So here is my table.

1/4/13

6

9

24

25

34

2

14

0

8