Welcome Guest
You last visited January 20, 2017, 12:40 am
All times shown are
Eastern Time (GMT-5:00)

Topic closed. 16 replies. Last post 11 years ago by LottoChica23.

 Page 1 of 2
Mid-Missouri
United States
Member #644
August 31, 2002
4271 Posts
Offline
 Posted: July 10, 2006, 7:01 pm - IP Logged

Howdy All,

Let me start by saying that I am completely lost when it comes to Excel but I am trying to learn. Any help is appreciated...
O.K. I have 2 questions. Here is what I am working on...

 Date Numbers Box 0 1 2 3 4 5 6 7 8 9 7/3/06 032 023 1 1 1 7/4/06 936 369 1 1 1 7/5/06 010 001 2 1 7/6/06 821 128 1 1 1 7/7/06 049 049 1 1 1 7/8/06 672 267 1 1 1 7/9/06 499 499 1 2

I need the formulas that can do this for me. When I put (032) in the first box (under Numbers) I would like the box number to be filled in automatically (under Box) and the tick sheet to the right to automatically make the appropriate marks.

Any and all help is appreciated.

Thanks,

Bryan  :)

United States
Member #8160
October 26, 2004
6777 Posts
Offline
 Posted: July 10, 2006, 8:05 pm - IP Logged

OK Bryan,

I do know that this has to be done in a few parts. First you have to break the digit number into 3 separate digits, meaning that 032 would become 0  3  2. This would take up 3 additional columns.  Assuming that we are using the number in the second column (B) to do this and you are and putting the formula in column C the formula would look like this: =INT(\$B3/100)    In column D the formula would look like this =INT((\$B3-(C3*100))/10)  In column E the formula would look like this  =INT((\$B3-((C3*100)+(D3*10)))/1)  after you get the formulas right, you can copy to the other cells in each column.

These formulas will isolate the 3 separate digits.  After that, you will have to write a formula to test each of the digits 0-9 against the 3 isloated digits to put a tick mark in the appropriate column. You should be able to do this using nested IF statements. Maybe somebody can shed some light on this or you can google working with nested IFs in excel.

To get the box for each number you can use the isloated digits to form the various box numbers.  You would substitute each cell reference in the formula =C3*100+D3*10+E3 to make up the various boxes. eg. CED, DEC, DCE, ECD, ECD

I hope this helps you accomplish what you want to do.

check out mysticwomyn Announcers --> http://www.lotterypost.com/thread/140695/673306

You can judge the integrity of a man by the way he treats those that can do nothing for him...

Mid-Missouri
United States
Member #644
August 31, 2002
4271 Posts
Offline
 Posted: July 10, 2006, 8:13 pm - IP Logged

Thank you for the info. I am printing this out for further reference. But Winsumloosesum has already fixed it for me.

Thanks again. It is so nice to know that help is available around every corner.

Best of Luck,

Bryan  :)

Redford/MI
United States
Member #3396
January 18, 2004
4867 Posts
Offline
 Posted: July 10, 2006, 10:42 pm - IP Logged

This is for those that do not know how to change this draw result into box form:

In columns B:D, I have the draw result.  ex: 3|3|5

Wherever you want the actual result (335) to end up is where you place this formula

=CONCATENATE(B2,C2,D2)

Then wherever you want the box form to end up is where you place this formula.

=CONCATENATE(SMALL(B2:D2,1),SMALL(B2:D2,2),SMALL(B2:D2,3))

L ttaL   T

NC
United States
Member #29378
January 1, 2006
552 Posts
Offline
 Posted: July 10, 2006, 11:31 pm - IP Logged

 Number 100's 10's 1's 0 1 2 3 4 5 6 7 8 9 7/3/2006 32 0 3 2 1 0 1 1 0 0 0 0 0 0 7/4/2006 936 9 3 6 0 0 0 1 0 0 1 0 0 1 7/5/2006 10 0 1 0 2 1 0 0 0 0 0 0 0 0 7/6/2006 821 8 2 1 0 1 1 0 0 0 0 0 1 0 7/7/2006 49 0 4 9 1 0 0 0 1 0 0 0 0 1

LottoChica23 already showed you how to break the numbers down individually, so here's your equation you need. I made it for "F2" & then copied & pasted it into the rest of the cells as needed.

=(COUNTIF(\$C2,F\$1)+COUNTIF(\$D2,F\$1)+COUNTIF(\$E2,F\$1))

-C2 is 0 in the 100's column, D2 is 3, E2 is 2

-F1 through O1 are just representative numbers we're going to test our numbers against

-F2 through O2 is where you copy and paste this formula to & as you notice I locked down the appropriate column aand/or row to prevent miscalculations as you copy & paste it through out the rest of your spreadsheet.

-Finally, you can go to the "Format" menu & use conditional formatting to highlight any cells greater than 0 as I did with the light green cells...

Again, I hope this helps you M8 & does what you want it to. Send us a reply when you crack "The Code"

;-) Pogo

Virginia
United States
Member #41886
June 24, 2006
500 Posts
Offline
 Posted: July 12, 2006, 11:33 pm - IP Logged

Bryan, can you send me when you are finished  those two numbers came out in Va 639 then the next night 128 came out this  tool would be very helpful. I need lessons on excel also my computer has Quattro Pro and I really don't know how to set up anything.

Thanks

New York, NY
United States
Member #39471
May 16, 2006
2698 Posts
Offline
 Posted: July 13, 2006, 7:46 am - IP Logged

Lottachiaca and Lottalott thanks for these Excel tips. Great info. I use Excel a lot (every day) but I have learned more on LP than the books i read and courses I took.

\$\$\$

Richmond/Virginia
United States
Member #14154
April 21, 2005
20 Posts
Offline
 Posted: July 13, 2006, 12:22 pm - IP Logged

How can you write an Excel formulas, that will calculate what numbers are coming more frequently than others in this sheet, with/out counting each column yourself? Would possibly make choosing your 3 numbers to play easier.

United States
Member #8160
October 26, 2004
6777 Posts
Offline
 Posted: July 14, 2006, 8:57 pm - IP Logged

You're welcome fibonacci.  In addition, when you have a number such as 095 and the leading zero does not appear in the cell, you can fix this by using a custom format (Format--> Cells--> Custom) of 000 to fix that problem. After doing this, all of your leading zeroes will show up.

check out mysticwomyn Announcers --> http://www.lotterypost.com/thread/140695/673306

You can judge the integrity of a man by the way he treats those that can do nothing for him...

Mid-Missouri
United States
Member #644
August 31, 2002
4271 Posts
Offline
 Posted: July 14, 2006, 9:15 pm - IP Logged

Thanks LC23,

I was wondering about that. I got to show up with formatting the cell to text. I didn't know if that would have any adverse effect or not. But the 0 stayed when I did that and that is all I was after...

Thanks to everyone for all the ideas and formulas.

Bryan  :)

NC
United States
Member #29378
January 1, 2006
552 Posts
Offline
 Posted: July 14, 2006, 11:15 pm - IP Logged

Bryan, it would be a good idea to reformat your numbers like LottoChica is saying, because having them in a text format will prevent them from showing up correctly when you decide to use formulas and the such. But if your not having problems with them formatted as text - Rock On!

Thanks for the tip on the leading 0 LottoChica - always bugged me that I didn't have a perfectly lined up columns of numbers...

Later, Pogo

United States
Member #8160
October 26, 2004
6777 Posts
Offline
 Posted: July 16, 2006, 8:28 am - IP Logged

Bryan, it would be a good idea to reformat your numbers like LottoChica is saying, because having them in a text format will prevent them from showing up correctly when you decide to use formulas and the such. But if your not having problems with them formatted as text - Rock On!

Thanks for the tip on the leading 0 LottoChica - always bugged me that I didn't have a perfectly lined up columns of numbers...

Later, Pogo

You're welcome Pogo, you learn something new every day. I'm still learning a few excel tricks here and there.

I remember back in the day when Excel used to be called Lotus 123, my has it grown up! (That may be before some folk's time... )

check out mysticwomyn Announcers --> http://www.lotterypost.com/thread/140695/673306

You can judge the integrity of a man by the way he treats those that can do nothing for him...

United States
Member #8160
October 26, 2004
6777 Posts
Offline
 Posted: July 16, 2006, 8:35 am - IP Logged

Thanks LC23,

I was wondering about that. I got to show up with formatting the cell to text. I didn't know if that would have any adverse effect or not. But the 0 stayed when I did that and that is all I was after...

Thanks to everyone for all the ideas and formulas.

Bryan  :)

You're welcome.  I know what you mean, I had that problem one day too and I knew there had to be a way to fix it.  Pogo is correct in saying that you may run into problems performing arithmetic computations on cells formatted as text. You will get unpredictable results.

Good luck with your worksheet and many hits to you.

check out mysticwomyn Announcers --> http://www.lotterypost.com/thread/140695/673306

You can judge the integrity of a man by the way he treats those that can do nothing for him...

New Jersey
United States
Member #17843
June 28, 2005
51098 Posts
Offline
 Posted: July 16, 2006, 8:51 am - IP Logged

Bryan, it would be a good idea to reformat your numbers like LottoChica is saying, because having them in a text format will prevent them from showing up correctly when you decide to use formulas and the such. But if your not having problems with them formatted as text - Rock On!

Thanks for the tip on the leading 0 LottoChica - always bugged me that I didn't have a perfectly lined up columns of numbers...

Later, Pogo

You're welcome Pogo, you learn something new every day. I'm still learning a few excel tricks here and there.

I remember back in the day when Excel used to be called Lotus 123, my has it grown up! (That may be before some folk's time... )

LOL! Lotus 123 is alive and well!

Toronto
Member #5142
June 20, 2004
47 Posts
Offline
 Posted: July 16, 2006, 9:02 am - IP Logged

Here are a couple of websites that I have found useful for excel ...the second of which deals with lottery information....and turn up your speakers for the 1st one.

http://www.datapigtechnologies.com/ExcelMain.htm

Some days I spell luck with an "F" ©2005

 Page 1 of 2