How to calculate frequency of lottery numbers?

X

Xray_Man

I'm new to Excel, so please explain things to this newbie in non-technical
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,D1:D100,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)
 
D

Don Guillett

I once played the Texas lotto and kept a log of winning numbers. Probably
best to use a macro to record as each game is played. However, my file
showed me that playing the lotto was not a winning proposition so I QUIT.
However, if desired, send your file to my address below and I'll take a
look.
 
A

AltaEgo

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.
 
M

Max

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
 
X

Xray_Man

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...
 
X

Xray_Man

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...
 
A

AltaEgo

My pleasure. I suspect you will enjoy building your lottery tool more than
spending the winnings <bg>
 
A

AltaEgo

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 1-59 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/en-us/excel/HP100704611033.aspx?pid=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 drop-down set to 'Cell value is'
D) Change the second drop-down 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/en-us/excel/HP100739391033.aspx?pid=CH100648451033
 
X

Xray_Man

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 said:
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 1-59 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/en-us/excel/HP100704611033.aspx?pid=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 drop-down set to 'Cell value is'
D) Change the second drop-down 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/en-us/excel/HP100739391033.aspx?pid=CH100648451033


--
Steve

Xray_Man said:
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...
 
A

AltaEgo

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.advanced-excel.com/index.html
http://www.datapigtechnologies.com/ExcelMain.htm
http://mistupid.com/tutorials/excel/
http://www.brainbell.com/tutorials/ms-office/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 said:
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 said:
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 1-59 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/en-us/excel/HP100704611033.aspx?pid=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 drop-down set to 'Cell value is'
D) Change the second drop-down 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/en-us/excel/HP100739391033.aspx?pid=CH100648451033


--
Steve

Xray_Man said:
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...


:

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

I'm new to Excel, so please explain things to this newbie in
non-technical
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,D1:D100,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)
 
X

Xray_Man

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 said:
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.advanced-excel.com/index.html
http://www.datapigtechnologies.com/ExcelMain.htm
http://mistupid.com/tutorials/excel/
http://www.brainbell.com/tutorials/ms-office/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 said:
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 said:
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 1-59 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/en-us/excel/HP100704611033.aspx?pid=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 drop-down set to 'Cell value is'
D) Change the second drop-down 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/en-us/excel/HP100739391033.aspx?pid=CH100648451033


--
Steve

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...


:

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

I'm new to Excel, so please explain things to this newbie in
non-technical
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,D1:D100,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)
 
X

Xray_Man

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
 
X

Xray_Man

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 said:
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 said:
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 1-59 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/en-us/excel/HP100704611033.aspx?pid=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 drop-down set to 'Cell value is'
D) Change the second drop-down 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/en-us/excel/HP100739391033.aspx?pid=CH100648451033


--
Steve

Xray_Man said:
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...


:

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

I'm new to Excel, so please explain things to this newbie in
non-technical
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,D1:D100,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)
 
D

Don Guillett

Don't forget to share.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Xray_Man said:
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 said:
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 said:
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 1-59 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/en-us/excel/HP100704611033.aspx?pid=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 drop-down set to 'Cell value is'
D) Change the second drop-down 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/en-us/excel/HP100739391033.aspx?pid=CH100648451033


--
Steve

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...


:

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

I'm new to Excel, so please explain things to this newbie in
non-technical
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,D1:D100,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)
 
X

Xray_Man

Absolutely! My wife will get HALF of my winnings!

Don Guillett said:
Don't forget to share.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Xray_Man said:
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 said:
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

:

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 1-59 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/en-us/excel/HP100704611033.aspx?pid=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 drop-down set to 'Cell value is'
D) Change the second drop-down 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/en-us/excel/HP100739391033.aspx?pid=CH100648451033


--
Steve

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...


:

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

I'm new to Excel, so please explain things to this newbie in
non-technical
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,D1:D100,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)
 
D

Don Guillett

Then, she won't need you.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Xray_Man said:
Absolutely! My wife will get HALF of my winnings!

Don Guillett said:
Don't forget to share.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Xray_Man said:
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

:

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

:

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 1-59 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/en-us/excel/HP100704611033.aspx?pid=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 drop-down set to 'Cell value is'
D) Change the second drop-down 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/en-us/excel/HP100739391033.aspx?pid=CH100648451033


--
Steve

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...


:

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

I'm new to Excel, so please explain things to this newbie in
non-technical
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,D1:D100,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)
 
A

AltaEgo

My calculations indicate that leaves 50% for Don!

--
Steve

Xray_Man said:
Absolutely! My wife will get HALF of my winnings!

Don Guillett said:
Don't forget to share.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Xray_Man said:
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

:

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

:

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 1-59 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/en-us/excel/HP100704611033.aspx?pid=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 drop-down set to 'Cell value is'
D) Change the second drop-down 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/en-us/excel/HP100739391033.aspx?pid=CH100648451033


--
Steve

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...


:

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

I'm new to Excel, so please explain things to this newbie in
non-technical
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,D1:D100,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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top