How do I count the times each number appears in columns of numbers?
Posted
by
Andy C.
on Super User
See other posts from Super User
or by Andy C.
Published on 2011-03-06T05:42:37Z
Indexed on
2011/03/06
8:12 UTC
Read the original article
Hit count: 255
spreadsheet
|sorting
I am sure this must be easy, but I am inexperienced. About the best way to think of my problem is to think of it as trying to sort and then count lottery numbers. To stay simple, let's do a Pick 3 game. Let's look at 10 drawings. I would split each drawn number into a separate column:
DATE BALL#1 BALL#2 BALL#3
3/1 1 3 5
3/2 3 7 8
3/3 2 2 1
3/4 5 7 6
3/5 2 3 1
3/6 0 5 9
3/7 3 7 0
3/8 6 8 4
3/9 2 4 3
3/10 7 1 2
I would like to be able to build formulas into cells that would tell me how many times each number appeared overall, and how many times each number appeared in the position it occurred.
Like this (using the above example):
Number Overall Count Ball#1 Count Ball#2 Count Ball#3 Count
0 2 1 0 1
1 4 1 1 2
(That is, The number zero appears twice overall, and came up once as the first number drawn; zero times as the middle ball; and once as the third ball. Likewise, the number 1 was drawn four times in our 10-day period. It was the first ball once, the second ball once and the third ball twice.)
And so on.
All help appreciated. I have access to Excel and Microsoft Works, or of course if there is a Google Docs way to handle this
All thanks for any help.
© Super User or respective owner