View Single Post
  #20   Report Post  
Posted to microsoft.public.excel.newusers
Don Guillett Don Guillett is offline
external usenet poster
 
Posts: 10,124
Default 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
...
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 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/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 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/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
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)