View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.newusers
AltaEgo AltaEgo is offline
external usenet poster
 
Posts: 245
Default How to calculate frequency of lottery numbers?

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)