Home 
Search 
Today's Posts 
#11




How to calculate frequency of lottery numbers?
Thanks again, Steve. I am currently going through the Excel basic training
courses on the Microsoft web site. I will also take a look at the links that you provided. I'd better learn how to walk before I can expect to run! If I manage to create a really cool lottery number frequency calculator, I'll post it here. But don't expect to see it in the near future! Take care... Mike "AltaEgo" wrote: If you aim to enjoy Excel, rather than make a fortune, I am sure you will get a lot out of the exercise. Meanwhile, the following resources may help you from time to time: In no particular order: http://www.contextures.com/tiptech.html (recommended) http://www.bettersolutions.com/excel.aspx http://www.advancedexcel.com/index.html http://www.datapigtechnologies.com/ExcelMain.htm http://mistupid.com/tutorials/excel/ http://www.brainbell.com/tutorials/msoffice/excel/ http://spreadsheetpage.com/index.php/tips http://www.mrexcel.com/articles.shtml Don't try to take it all in at once. Grow your knowledge as you need to use it.  Steve "Xray_Man" wrote in message ... Thanks again, Steve! I'll give that a try. Because I'm new to Excel, it may be a bit of a struggle, but it's a program that I've been wanting to learn for quite some time, and this seems like a good practice project that will get my feet wet! Later.... Mike "AltaEgo" wrote: OK, I understand the problem. My view is that charting is overly complex. However, this does not mean you cannot see the result graphically. An alternatives to charting: 1) To the right of you raw data, create an array that counts the number of times each number appears each week (i.e. so it shows 1 or 0). 2) Set the spreadsheet to not show zero values 3) Conditional format the area to highlight cells with a value = 1 How to: Set up you spreadsheet so it has a single row above your data Set up your numbers 159 across row 1 of the spreadsheet. Starting at column K1, enter 1, at L1, enter 2, etc. up to BQ which will have the value 59. In K2, enter the formula "=COUNTIF($A2:$E2,K$1) Copy this so it appears in all cells from K2 to BQ101. Instructions below relate to Excel 2003: To suppress zero values A) Select Menu 'Tools', Command 'Options', Tab 'View' B) Under 'Window options' uncheck 'Zero values' XL2007 http://office.microsoft.com/enus/ex...CH100793441033 To conditionally format A) Select the whole range of your counts (K2 to BQ101) B) Select Menu 'Format', Command 'Conditional formatting...' C) Leave the first dropdown set to 'Cell value is' D) Change the second dropdown to 'equal to' E) In the next (blank) dialogue box type 1 F) Click the [Format...] button and choose your format colour from the 'patterns' tab. G) Click [OK] twice to see the result. XL2007 http://office.microsoft.com/enus/ex...CH100648451033  Steve "Xray_Man" wrote in message ... Okay, AltaEgo, I'm still having a problem making it work. Maybe if I explain what I want to do a little differently, it would help. I am looking at the last 100 PowerBall games, and I want to calculate how often the numbers 1 through 59 have shown up in those 100 games. There are 5 white balls drawn in each game, and each ball can have a value between 1 and 59. So, if say number 23 popped up 31 times over the last 100 games, I want to some how visualize it. That's why I thought the Frequency function would be the right function to use. I downloaded the last 100 games into arrays A1 through E100, where A1 through A100 represents the first of 5 balls drawn, then B1 through B100 represents the second of 5 balls drawn, etc. I used K1 to K59 just to have an array of numbers from 1 to 59. Got me so far? Now, what I want to do is to some how end up with either an array of frequencies or some sort of histogram (like a bar chart) to visually display the frequencies as heights of the bars in the bargraph. Then I could play the numbers with the highest frequencies from the last 100 games. I realize that this scheme probably won't make me a zillionairre, but it might give me a slight (and I mean VERY slight) edge over letting the computer give me a random pick. Any input would be much appreciated. THANKS again... "AltaEgo" wrote: Unless I misunderstand the question, you seem to be seeking a count of the number of times each number appears in the range A1:E100. If so, in L1 enter the formula: =COUNTIF($A$1:$E$100,K1) Copy the formula down to K59. We all know whoever runs the lottery takes away the biggest prize but hope springs eternal.  Steve "Xray_Man" wrote in message ... I'm new to Excel, so please explain things to this newbie in nontechnical terms as much as possible! I have downloaded the array of previous week's winning lottery numbers, and I want Excel to create a number frequency chart for me. The numbers are 1 to 59. I want to calculate a histogram of the frequencies of each number from 1 to 59. So far, I set up Excel so that the results of the previous lottery games are in 5 collumns. A1:A100,B1:B100,C1:C100,D1100,E1:E100. I then entered the numbers 1 through 59 into K1:K59. I tried to use the Frequency function, which sort of seems to calculate the frequencies as expected, but I don't know where to find the resulting array of frequencies. I hope what I just said didn't sound too silly, but my problem is that I don't know how to complete the job so that I can some how view the array of frequencies in some easy to view form, such as a histogram. Any help would be most appreciated (and I hope it makes me rich!! LOL) 
#12




How to calculate frequency of lottery numbers?
Concur  Don Guillett Microsoft MVP Excel SalesAid Software "Max" wrote in message ... When you think of it, its pointless to put in so much effort analysing, since its basically memoryless from one draw to the next. Doesn't mean the frequent ones are better than the infrequent/cold. For an easy flutter to one's budget, I'd just ask at the sales counter for a computer pick or two, and be done with it. Don't even need to mark any forms, etc.  Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik  "Xray_Man" wrote: I'm new to Excel, so please explain things to this newbie in nontechnical terms as much as possible! I have downloaded the array of previous week's winning lottery numbers, and I want Excel to create a number frequency chart for me. The numbers are 1 to 59. I want to calculate a histogram of the frequencies of each number from 1 to 59. So far, I set up Excel so that the results of the previous lottery games are in 5 collumns. A1:A100,B1:B100,C1:C100,D1100,E1:E100. I then entered the numbers 1 through 59 into K1:K59. I tried to use the Frequency function, which sort of seems to calculate the frequencies as expected, but I don't know where to find the resulting array of frequencies. I hope what I just said didn't sound too silly, but my problem is that I don't know how to complete the job so that I can some how view the array of frequencies in some easy to view form, such as a histogram. Any help would be most appreciated (and I hope it makes me rich!! LOL) 
#14




How to calculate frequency of lottery numbers?
Hey Don,
The frequency charts on the PowerBall web site covers a range of games that extends from the year 2005 to the present. My belief is that games way back in 2005 have no bearing whatsoever on current draws. But there "may" be a slight dependency or correlation with draws from recent (the previous 100) games. It's just my own theory, as I am not a statistician by any stretch of the imagination. That's why I want to calculate and display the results of a frequency array from the previous 100 games. Thanks for you help! Mike "Don Guillett" wrote: http://www.powerball.com/powerball/pb_frequency.asp Ball Number White Balls Frequency 12 40 40 40 43 40 26 39 2 38 8 38 13 38 19 38 30 38 48 38 24 37 55 37 14 36  Don Guillett Microsoft MVP Excel SalesAid Software "Xray_Man" wrote in message ... Thanks AltaEgo! You gave me the help that I was looking for, although I still may have more questions after working with it some more. The other posters did not provide any help. They were mainly concerned about the philosophy of whether or not playing the lottery is a right or wrong thing to do. I wasn't looking for a lecture in morality. <roll eyes Later... "AltaEgo" wrote: Unless I misunderstand the question, you seem to be seeking a count of the number of times each number appears in the range A1:E100. If so, in L1 enter the formula: =COUNTIF($A$1:$E$100,K1) Copy the formula down to K59. We all know whoever runs the lottery takes away the biggest prize but hope springs eternal.  Steve "Xray_Man" wrote in message ... I'm new to Excel, so please explain things to this newbie in nontechnical terms as much as possible! I have downloaded the array of previous week's winning lottery numbers, and I want Excel to create a number frequency chart for me. The numbers are 1 to 59. I want to calculate a histogram of the frequencies of each number from 1 to 59. So far, I set up Excel so that the results of the previous lottery games are in 5 collumns. A1:A100,B1:B100,C1:C100,D1100,E1:E100. I then entered the numbers 1 through 59 into K1:K59. I tried to use the Frequency function, which sort of seems to calculate the frequencies as expected, but I don't know where to find the resulting array of frequencies. I hope what I just said didn't sound too silly, but my problem is that I don't know how to complete the job so that I can some how view the array of frequencies in some easy to view form, such as a histogram. Any help would be most appreciated (and I hope it makes me rich!! LOL) 
#15




How to calculate frequency of lottery numbers?
Re read Max's. However, if you insist, I can custom design a program for
you.  Don Guillett Microsoft MVP Excel SalesAid Software "Xray_Man" wrote in message ... Hey Don, The frequency charts on the PowerBall web site covers a range of games that extends from the year 2005 to the present. My belief is that games way back in 2005 have no bearing whatsoever on current draws. But there "may" be a slight dependency or correlation with draws from recent (the previous 100) games. It's just my own theory, as I am not a statistician by any stretch of the imagination. That's why I want to calculate and display the results of a frequency array from the previous 100 games. Thanks for you help! Mike "Don Guillett" wrote: http://www.powerball.com/powerball/pb_frequency.asp Ball Number White Balls Frequency 12 40 40 40 43 40 26 39 2 38 8 38 13 38 19 38 30 38 48 38 24 37 55 37 14 36  Don Guillett Microsoft MVP Excel SalesAid Software "Xray_Man" wrote in message ... Thanks AltaEgo! You gave me the help that I was looking for, although I still may have more questions after working with it some more. The other posters did not provide any help. They were mainly concerned about the philosophy of whether or not playing the lottery is a right or wrong thing to do. I wasn't looking for a lecture in morality. <roll eyes Later... "AltaEgo" wrote: Unless I misunderstand the question, you seem to be seeking a count of the number of times each number appears in the range A1:E100. If so, in L1 enter the formula: =COUNTIF($A$1:$E$100,K1) Copy the formula down to K59. We all know whoever runs the lottery takes away the biggest prize but hope springs eternal.  Steve "Xray_Man" wrote in message ... I'm new to Excel, so please explain things to this newbie in nontechnical terms as much as possible! I have downloaded the array of previous week's winning lottery numbers, and I want Excel to create a number frequency chart for me. The numbers are 1 to 59. I want to calculate a histogram of the frequencies of each number from 1 to 59. So far, I set up Excel so that the results of the previous lottery games are in 5 collumns. A1:A100,B1:B100,C1:C100,D1100,E1:E100. I then entered the numbers 1 through 59 into K1:K59. I tried to use the Frequency function, which sort of seems to calculate the frequencies as expected, but I don't know where to find the resulting array of frequencies. I hope what I just said didn't sound too silly, but my problem is that I don't know how to complete the job so that I can some how view the array of frequencies in some easy to view form, such as a histogram. Any help would be most appreciated (and I hope it makes me rich!! LOL) 
#16




How to calculate frequency of lottery numbers?
success!!!!! I now have the frequency spectrum chart that I was looking for!
Here's how I did it.... I downloaded the previous 100 PowerBall games from the PowerBall web site. I plugged all the white ball drawings into the following arrays: The first number of the 5 drawn balls went into array A1:A100, then second number went into B1:B100, etc. I then manually entered the numbers 1 through 59 (the range of numbers for the white balls) into array K1:K59. I then selected a blank array M1:M59 which will hold the results of the calculation. I then executed the Frequency function: Frequency(A1:E100,K1:K59). I then plotted the results using one of the Excel bar chart functions. Now I'm on the road to RICHES!!!! LOL Many thanks to everyone who contributed. Mike "Xray_Man" wrote: Thanks again, Steve! I'll give that a try. Because I'm new to Excel, it may be a bit of a struggle, but it's a program that I've been wanting to learn for quite some time, and this seems like a good practice project that will get my feet wet! Later.... Mike "AltaEgo" wrote: OK, I understand the problem. My view is that charting is overly complex. However, this does not mean you cannot see the result graphically. An alternatives to charting: 1) To the right of you raw data, create an array that counts the number of times each number appears each week (i.e. so it shows 1 or 0). 2) Set the spreadsheet to not show zero values 3) Conditional format the area to highlight cells with a value = 1 How to: Set up you spreadsheet so it has a single row above your data Set up your numbers 159 across row 1 of the spreadsheet. Starting at column K1, enter 1, at L1, enter 2, etc. up to BQ which will have the value 59. In K2, enter the formula "=COUNTIF($A2:$E2,K$1) Copy this so it appears in all cells from K2 to BQ101. Instructions below relate to Excel 2003: To suppress zero values A) Select Menu 'Tools', Command 'Options', Tab 'View' B) Under 'Window options' uncheck 'Zero values' XL2007 http://office.microsoft.com/enus/ex...CH100793441033 To conditionally format A) Select the whole range of your counts (K2 to BQ101) B) Select Menu 'Format', Command 'Conditional formatting...' C) Leave the first dropdown set to 'Cell value is' D) Change the second dropdown to 'equal to' E) In the next (blank) dialogue box type 1 F) Click the [Format...] button and choose your format colour from the 'patterns' tab. G) Click [OK] twice to see the result. XL2007 http://office.microsoft.com/enus/ex...CH100648451033  Steve "Xray_Man" wrote in message ... Okay, AltaEgo, I'm still having a problem making it work. Maybe if I explain what I want to do a little differently, it would help. I am looking at the last 100 PowerBall games, and I want to calculate how often the numbers 1 through 59 have shown up in those 100 games. There are 5 white balls drawn in each game, and each ball can have a value between 1 and 59. So, if say number 23 popped up 31 times over the last 100 games, I want to some how visualize it. That's why I thought the Frequency function would be the right function to use. I downloaded the last 100 games into arrays A1 through E100, where A1 through A100 represents the first of 5 balls drawn, then B1 through B100 represents the second of 5 balls drawn, etc. I used K1 to K59 just to have an array of numbers from 1 to 59. Got me so far? Now, what I want to do is to some how end up with either an array of frequencies or some sort of histogram (like a bar chart) to visually display the frequencies as heights of the bars in the bargraph. Then I could play the numbers with the highest frequencies from the last 100 games. I realize that this scheme probably won't make me a zillionairre, but it might give me a slight (and I mean VERY slight) edge over letting the computer give me a random pick. Any input would be much appreciated. THANKS again... "AltaEgo" wrote: Unless I misunderstand the question, you seem to be seeking a count of the number of times each number appears in the range A1:E100. If so, in L1 enter the formula: =COUNTIF($A$1:$E$100,K1) Copy the formula down to K59. We all know whoever runs the lottery takes away the biggest prize but hope springs eternal.  Steve "Xray_Man" wrote in message ... I'm new to Excel, so please explain things to this newbie in nontechnical terms as much as possible! I have downloaded the array of previous week's winning lottery numbers, and I want Excel to create a number frequency chart for me. The numbers are 1 to 59. I want to calculate a histogram of the frequencies of each number from 1 to 59. So far, I set up Excel so that the results of the previous lottery games are in 5 collumns. A1:A100,B1:B100,C1:C100,D1100,E1:E100. I then entered the numbers 1 through 59 into K1:K59. I tried to use the Frequency function, which sort of seems to calculate the frequencies as expected, but I don't know where to find the resulting array of frequencies. I hope what I just said didn't sound too silly, but my problem is that I don't know how to complete the job so that I can some how view the array of frequencies in some easy to view form, such as a histogram. Any help would be most appreciated (and I hope it makes me rich!! LOL) 
#17




How to calculate frequency of lottery numbers?
Don't forget to share.
 Don Guillett Microsoft MVP Excel SalesAid Software "Xray_Man" wrote in message ... success!!!!! I now have the frequency spectrum chart that I was looking for! Here's how I did it.... I downloaded the previous 100 PowerBall games from the PowerBall web site. I plugged all the white ball drawings into the following arrays: The first number of the 5 drawn balls went into array A1:A100, then second number went into B1:B100, etc. I then manually entered the numbers 1 through 59 (the range of numbers for the white balls) into array K1:K59. I then selected a blank array M1:M59 which will hold the results of the calculation. I then executed the Frequency function: Frequency(A1:E100,K1:K59). I then plotted the results using one of the Excel bar chart functions. Now I'm on the road to RICHES!!!! LOL Many thanks to everyone who contributed. Mike "Xray_Man" wrote: Thanks again, Steve! I'll give that a try. Because I'm new to Excel, it may be a bit of a struggle, but it's a program that I've been wanting to learn for quite some time, and this seems like a good practice project that will get my feet wet! Later.... Mike "AltaEgo" wrote: OK, I understand the problem. My view is that charting is overly complex. However, this does not mean you cannot see the result graphically. An alternatives to charting: 1) To the right of you raw data, create an array that counts the number of times each number appears each week (i.e. so it shows 1 or 0). 2) Set the spreadsheet to not show zero values 3) Conditional format the area to highlight cells with a value = 1 How to: Set up you spreadsheet so it has a single row above your data Set up your numbers 159 across row 1 of the spreadsheet. Starting at column K1, enter 1, at L1, enter 2, etc. up to BQ which will have the value 59. In K2, enter the formula "=COUNTIF($A2:$E2,K$1) Copy this so it appears in all cells from K2 to BQ101. Instructions below relate to Excel 2003: To suppress zero values A) Select Menu 'Tools', Command 'Options', Tab 'View' B) Under 'Window options' uncheck 'Zero values' XL2007 http://office.microsoft.com/enus/ex...CH100793441033 To conditionally format A) Select the whole range of your counts (K2 to BQ101) B) Select Menu 'Format', Command 'Conditional formatting...' C) Leave the first dropdown set to 'Cell value is' D) Change the second dropdown to 'equal to' E) In the next (blank) dialogue box type 1 F) Click the [Format...] button and choose your format colour from the 'patterns' tab. G) Click [OK] twice to see the result. XL2007 http://office.microsoft.com/enus/ex...CH100648451033  Steve "Xray_Man" wrote in message ... Okay, AltaEgo, I'm still having a problem making it work. Maybe if I explain what I want to do a little differently, it would help. I am looking at the last 100 PowerBall games, and I want to calculate how often the numbers 1 through 59 have shown up in those 100 games. There are 5 white balls drawn in each game, and each ball can have a value between 1 and 59. So, if say number 23 popped up 31 times over the last 100 games, I want to some how visualize it. That's why I thought the Frequency function would be the right function to use. I downloaded the last 100 games into arrays A1 through E100, where A1 through A100 represents the first of 5 balls drawn, then B1 through B100 represents the second of 5 balls drawn, etc. I used K1 to K59 just to have an array of numbers from 1 to 59. Got me so far? Now, what I want to do is to some how end up with either an array of frequencies or some sort of histogram (like a bar chart) to visually display the frequencies as heights of the bars in the bargraph. Then I could play the numbers with the highest frequencies from the last 100 games. I realize that this scheme probably won't make me a zillionairre, but it might give me a slight (and I mean VERY slight) edge over letting the computer give me a random pick. Any input would be much appreciated. THANKS again... "AltaEgo" wrote: Unless I misunderstand the question, you seem to be seeking a count of the number of times each number appears in the range A1:E100. If so, in L1 enter the formula: =COUNTIF($A$1:$E$100,K1) Copy the formula down to K59. We all know whoever runs the lottery takes away the biggest prize but hope springs eternal.  Steve "Xray_Man" wrote in message ... I'm new to Excel, so please explain things to this newbie in nontechnical terms as much as possible! I have downloaded the array of previous week's winning lottery numbers, and I want Excel to create a number frequency chart for me. The numbers are 1 to 59. I want to calculate a histogram of the frequencies of each number from 1 to 59. So far, I set up Excel so that the results of the previous lottery games are in 5 collumns. A1:A100,B1:B100,C1:C100,D1100,E1:E100. I then entered the numbers 1 through 59 into K1:K59. I tried to use the Frequency function, which sort of seems to calculate the frequencies as expected, but I don't know where to find the resulting array of frequencies. I hope what I just said didn't sound too silly, but my problem is that I don't know how to complete the job so that I can some how view the array of frequencies in some easy to view form, such as a histogram. Any help would be most appreciated (and I hope it makes me rich!! LOL) 
#18




How to calculate frequency of lottery numbers?
Absolutely! My wife will get HALF of my winnings!
"Don Guillett" wrote: Don't forget to share.  Don Guillett Microsoft MVP Excel SalesAid Software "Xray_Man" wrote in message ... success!!!!! I now have the frequency spectrum chart that I was looking for! Here's how I did it.... I downloaded the previous 100 PowerBall games from the PowerBall web site. I plugged all the white ball drawings into the following arrays: The first number of the 5 drawn balls went into array A1:A100, then second number went into B1:B100, etc. I then manually entered the numbers 1 through 59 (the range of numbers for the white balls) into array K1:K59. I then selected a blank array M1:M59 which will hold the results of the calculation. I then executed the Frequency function: Frequency(A1:E100,K1:K59). I then plotted the results using one of the Excel bar chart functions. Now I'm on the road to RICHES!!!! LOL Many thanks to everyone who contributed. Mike "Xray_Man" wrote: Thanks again, Steve! I'll give that a try. Because I'm new to Excel, it may be a bit of a struggle, but it's a program that I've been wanting to learn for quite some time, and this seems like a good practice project that will get my feet wet! Later.... Mike "AltaEgo" wrote: OK, I understand the problem. My view is that charting is overly complex. However, this does not mean you cannot see the result graphically. An alternatives to charting: 1) To the right of you raw data, create an array that counts the number of times each number appears each week (i.e. so it shows 1 or 0). 2) Set the spreadsheet to not show zero values 3) Conditional format the area to highlight cells with a value = 1 How to: Set up you spreadsheet so it has a single row above your data Set up your numbers 159 across row 1 of the spreadsheet. Starting at column K1, enter 1, at L1, enter 2, etc. up to BQ which will have the value 59. In K2, enter the formula "=COUNTIF($A2:$E2,K$1) Copy this so it appears in all cells from K2 to BQ101. Instructions below relate to Excel 2003: To suppress zero values A) Select Menu 'Tools', Command 'Options', Tab 'View' B) Under 'Window options' uncheck 'Zero values' XL2007 http://office.microsoft.com/enus/ex...CH100793441033 To conditionally format A) Select the whole range of your counts (K2 to BQ101) B) Select Menu 'Format', Command 'Conditional formatting...' C) Leave the first dropdown set to 'Cell value is' D) Change the second dropdown to 'equal to' E) In the next (blank) dialogue box type 1 F) Click the [Format...] button and choose your format colour from the 'patterns' tab. G) Click [OK] twice to see the result. XL2007 http://office.microsoft.com/enus/ex...CH100648451033  Steve "Xray_Man" wrote in message ... Okay, AltaEgo, I'm still having a problem making it work. Maybe if I explain what I want to do a little differently, it would help. I am looking at the last 100 PowerBall games, and I want to calculate how often the numbers 1 through 59 have shown up in those 100 games. There are 5 white balls drawn in each game, and each ball can have a value between 1 and 59. So, if say number 23 popped up 31 times over the last 100 games, I want to some how visualize it. That's why I thought the Frequency function would be the right function to use. I downloaded the last 100 games into arrays A1 through E100, where A1 through A100 represents the first of 5 balls drawn, then B1 through B100 represents the second of 5 balls drawn, etc. I used K1 to K59 just to have an array of numbers from 1 to 59. Got me so far? Now, what I want to do is to some how end up with either an array of frequencies or some sort of histogram (like a bar chart) to visually display the frequencies as heights of the bars in the bargraph. Then I could play the numbers with the highest frequencies from the last 100 games. I realize that this scheme probably won't make me a zillionairre, but it might give me a slight (and I mean VERY slight) edge over letting the computer give me a random pick. Any input would be much appreciated. THANKS again... "AltaEgo" wrote: Unless I misunderstand the question, you seem to be seeking a count of the number of times each number appears in the range A1:E100. If so, in L1 enter the formula: =COUNTIF($A$1:$E$100,K1) Copy the formula down to K59. We all know whoever runs the lottery takes away the biggest prize but hope springs eternal.  Steve "Xray_Man" wrote in message ... I'm new to Excel, so please explain things to this newbie in nontechnical terms as much as possible! I have downloaded the array of previous week's winning lottery numbers, and I want Excel to create a number frequency chart for me. The numbers are 1 to 59. I want to calculate a histogram of the frequencies of each number from 1 to 59. So far, I set up Excel so that the results of the previous lottery games are in 5 collumns. A1:A100,B1:B100,C1:C100,D1100,E1:E100. I then entered the numbers 1 through 59 into K1:K59. I tried to use the Frequency function, which sort of seems to calculate the frequencies as expected, but I don't know where to find the resulting array of frequencies. I hope what I just said didn't sound too silly, but my problem is that I don't know how to complete the job so that I can some how view the array of frequencies in some easy to view form, such as a histogram. Any help would be most appreciated (and I hope it makes me rich!! LOL) 
#19




How to calculate frequency of lottery numbers?
Then, she won't need you.  Don Guillett Microsoft MVP Excel SalesAid Software "Xray_Man" wrote in message news Absolutely! My wife will get HALF of my winnings! "Don Guillett" wrote: Don't forget to share.  Don Guillett Microsoft MVP Excel SalesAid Software "Xray_Man" wrote in message ... success!!!!! I now have the frequency spectrum chart that I was looking for! Here's how I did it.... I downloaded the previous 100 PowerBall games from the PowerBall web site. I plugged all the white ball drawings into the following arrays: The first number of the 5 drawn balls went into array A1:A100, then second number went into B1:B100, etc. I then manually entered the numbers 1 through 59 (the range of numbers for the white balls) into array K1:K59. I then selected a blank array M1:M59 which will hold the results of the calculation. I then executed the Frequency function: Frequency(A1:E100,K1:K59). I then plotted the results using one of the Excel bar chart functions. Now I'm on the road to RICHES!!!! LOL Many thanks to everyone who contributed. Mike "Xray_Man" wrote: Thanks again, Steve! I'll give that a try. Because I'm new to Excel, it may be a bit of a struggle, but it's a program that I've been wanting to learn for quite some time, and this seems like a good practice project that will get my feet wet! Later.... Mike "AltaEgo" wrote: OK, I understand the problem. My view is that charting is overly complex. However, this does not mean you cannot see the result graphically. An alternatives to charting: 1) To the right of you raw data, create an array that counts the number of times each number appears each week (i.e. so it shows 1 or 0). 2) Set the spreadsheet to not show zero values 3) Conditional format the area to highlight cells with a value = 1 How to: Set up you spreadsheet so it has a single row above your data Set up your numbers 159 across row 1 of the spreadsheet. Starting at column K1, enter 1, at L1, enter 2, etc. up to BQ which will have the value 59. In K2, enter the formula "=COUNTIF($A2:$E2,K$1) Copy this so it appears in all cells from K2 to BQ101. Instructions below relate to Excel 2003: To suppress zero values A) Select Menu 'Tools', Command 'Options', Tab 'View' B) Under 'Window options' uncheck 'Zero values' XL2007 http://office.microsoft.com/enus/ex...CH100793441033 To conditionally format A) Select the whole range of your counts (K2 to BQ101) B) Select Menu 'Format', Command 'Conditional formatting...' C) Leave the first dropdown set to 'Cell value is' D) Change the second dropdown to 'equal to' E) In the next (blank) dialogue box type 1 F) Click the [Format...] button and choose your format colour from the 'patterns' tab. G) Click [OK] twice to see the result. XL2007 http://office.microsoft.com/enus/ex...CH100648451033  Steve "Xray_Man" wrote in message ... Okay, AltaEgo, I'm still having a problem making it work. Maybe if I explain what I want to do a little differently, it would help. I am looking at the last 100 PowerBall games, and I want to calculate how often the numbers 1 through 59 have shown up in those 100 games. There are 5 white balls drawn in each game, and each ball can have a value between 1 and 59. So, if say number 23 popped up 31 times over the last 100 games, I want to some how visualize it. That's why I thought the Frequency function would be the right function to use. I downloaded the last 100 games into arrays A1 through E100, where A1 through A100 represents the first of 5 balls drawn, then B1 through B100 represents the second of 5 balls drawn, etc. I used K1 to K59 just to have an array of numbers from 1 to 59. Got me so far? Now, what I want to do is to some how end up with either an array of frequencies or some sort of histogram (like a bar chart) to visually display the frequencies as heights of the bars in the bargraph. Then I could play the numbers with the highest frequencies from the last 100 games. I realize that this scheme probably won't make me a zillionairre, but it might give me a slight (and I mean VERY slight) edge over letting the computer give me a random pick. Any input would be much appreciated. THANKS again... "AltaEgo" wrote: Unless I misunderstand the question, you seem to be seeking a count of the number of times each number appears in the range A1:E100. If so, in L1 enter the formula: =COUNTIF($A$1:$E$100,K1) Copy the formula down to K59. We all know whoever runs the lottery takes away the biggest prize but hope springs eternal.  Steve "Xray_Man" wrote in message ... I'm new to Excel, so please explain things to this newbie in nontechnical terms as much as possible! I have downloaded the array of previous week's winning lottery numbers, and I want Excel to create a number frequency chart for me. The numbers are 1 to 59. I want to calculate a histogram of the frequencies of each number from 1 to 59. So far, I set up Excel so that the results of the previous lottery games are in 5 collumns. A1:A100,B1:B100,C1:C100,D1100,E1:E100. I then entered the numbers 1 through 59 into K1:K59. I tried to use the Frequency function, which sort of seems to calculate the frequencies as expected, but I don't know where to find the resulting array of frequencies. I hope what I just said didn't sound too silly, but my problem is that I don't know how to complete the job so that I can some how view the array of frequencies in some easy to view form, such as a histogram. Any help would be most appreciated (and I hope it makes me rich!! LOL) 
#20




How to calculate frequency of lottery numbers?
My calculations indicate that leaves 50% for Don!
 Steve "Xray_Man" wrote in message news Absolutely! My wife will get HALF of my winnings! "Don Guillett" wrote: Don't forget to share.  Don Guillett Microsoft MVP Excel SalesAid Software "Xray_Man" wrote in message ... success!!!!! I now have the frequency spectrum chart that I was looking for! Here's how I did it.... I downloaded the previous 100 PowerBall games from the PowerBall web site. I plugged all the white ball drawings into the following arrays: The first number of the 5 drawn balls went into array A1:A100, then second number went into B1:B100, etc. I then manually entered the numbers 1 through 59 (the range of numbers for the white balls) into array K1:K59. I then selected a blank array M1:M59 which will hold the results of the calculation. I then executed the Frequency function: Frequency(A1:E100,K1:K59). I then plotted the results using one of the Excel bar chart functions. Now I'm on the road to RICHES!!!! LOL Many thanks to everyone who contributed. Mike "Xray_Man" wrote: Thanks again, Steve! I'll give that a try. Because I'm new to Excel, it may be a bit of a struggle, but it's a program that I've been wanting to learn for quite some time, and this seems like a good practice project that will get my feet wet! Later.... Mike "AltaEgo" wrote: OK, I understand the problem. My view is that charting is overly complex. However, this does not mean you cannot see the result graphically. An alternatives to charting: 1) To the right of you raw data, create an array that counts the number of times each number appears each week (i.e. so it shows 1 or 0). 2) Set the spreadsheet to not show zero values 3) Conditional format the area to highlight cells with a value = 1 How to: Set up you spreadsheet so it has a single row above your data Set up your numbers 159 across row 1 of the spreadsheet. Starting at column K1, enter 1, at L1, enter 2, etc. up to BQ which will have the value 59. In K2, enter the formula "=COUNTIF($A2:$E2,K$1) Copy this so it appears in all cells from K2 to BQ101. Instructions below relate to Excel 2003: To suppress zero values A) Select Menu 'Tools', Command 'Options', Tab 'View' B) Under 'Window options' uncheck 'Zero values' XL2007 http://office.microsoft.com/enus/ex...CH100793441033 To conditionally format A) Select the whole range of your counts (K2 to BQ101) B) Select Menu 'Format', Command 'Conditional formatting...' C) Leave the first dropdown set to 'Cell value is' D) Change the second dropdown to 'equal to' E) In the next (blank) dialogue box type 1 F) Click the [Format...] button and choose your format colour from the 'patterns' tab. G) Click [OK] twice to see the result. XL2007 http://office.microsoft.com/enus/ex...CH100648451033  Steve "Xray_Man" wrote in message ... Okay, AltaEgo, I'm still having a problem making it work. Maybe if I explain what I want to do a little differently, it would help. I am looking at the last 100 PowerBall games, and I want to calculate how often the numbers 1 through 59 have shown up in those 100 games. There are 5 white balls drawn in each game, and each ball can have a value between 1 and 59. So, if say number 23 popped up 31 times over the last 100 games, I want to some how visualize it. That's why I thought the Frequency function would be the right function to use. I downloaded the last 100 games into arrays A1 through E100, where A1 through A100 represents the first of 5 balls drawn, then B1 through B100 represents the second of 5 balls drawn, etc. I used K1 to K59 just to have an array of numbers from 1 to 59. Got me so far? Now, what I want to do is to some how end up with either an array of frequencies or some sort of histogram (like a bar chart) to visually display the frequencies as heights of the bars in the bargraph. Then I could play the numbers with the highest frequencies from the last 100 games. I realize that this scheme probably won't make me a zillionairre, but it might give me a slight (and I mean VERY slight) edge over letting the computer give me a random pick. Any input would be much appreciated. THANKS again... "AltaEgo" wrote: Unless I misunderstand the question, you seem to be seeking a count of the number of times each number appears in the range A1:E100. If so, in L1 enter the formula: =COUNTIF($A$1:$E$100,K1) Copy the formula down to K59. We all know whoever runs the lottery takes away the biggest prize but hope springs eternal.  Steve "Xray_Man" wrote in message ... I'm new to Excel, so please explain things to this newbie in nontechnical terms as much as possible! I have downloaded the array of previous week's winning lottery numbers, and I want Excel to create a number frequency chart for me. The numbers are 1 to 59. I want to calculate a histogram of the frequencies of each number from 1 to 59. So far, I set up Excel so that the results of the previous lottery games are in 5 collumns. A1:A100,B1:B100,C1:C100,D1100,E1:E100. I then entered the numbers 1 through 59 into K1:K59. I tried to use the Frequency function, which sort of seems to calculate the frequencies as expected, but I don't know where to find the resulting array of frequencies. I hope what I just said didn't sound too silly, but my problem is that I don't know how to complete the job so that I can some how view the array of frequencies in some easy to view form, such as a histogram. Any help would be most appreciated (and I hope it makes me rich!! LOL) 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Calculate relative frequency?  Excel Worksheet Functions  
Bingo Lottery Winning Numbers  Excel Discussion (Misc queries)  
How do I generate random lottery numbers in Excel?  Excel Worksheet Functions  
Checking Winning Numbers in the Lottery.  Excel Discussion (Misc queries)  
complex validation for lottery numbers  Excel Discussion (Misc queries) 