How can I do this?

J

Just Me

I'm sure someone has done this with Excel before but I can't find anything
on it. I have a wooksheet that has the powerball numbers from 1992 to
present, each number has it's own column.
I want to find a formula of some kind that will tell me what number comes
up the most often in each column, also as I add more numbers to my worksheet
I would like it to update. I'd like the results of each column to be in the
first cell in each column.

Thanks in advance
 
J

John Bundy

There are more elegant ways, but I would just do a count of all column if it
contains a 1,2,3 to 9
=COUNTIF(B:D,1)
=COUNTIF(B:D,2)
=COUNTIF(B:D,3)
etc then you have a running total
 
K

KC Rippstein

=MODE(A$2:INDEX(A$2:A$65536,LOOKUP(2,1/(A$2:A$65536<>""),A$2:A$65536)))
Put that in cell A1 and then use the fill handle to drag that across to B1,
C1, etc.
- KC
 
J

Just Me

I did as you say but I get #VALUE !
Column A is the date b- h contain the numbers if that makes a difference
 
K

KC Rippstein

I assumed the numbers started in A2. If your numbers start in B2, then the
formula should still work when you use the fill handle to drag across from
A1 to B1, C1, D1, E1, F1, G1, and H1. After that, just go back and delete
the formula from A1.
If your numbers actually start in B3 (not B2), then change the $2 to $3
every place you see it in the formula.
- KC
 
K

KC Rippstein

In B1, =MODE(B$5:B$65536) and then use the fill handle to drag that formula
across to G1. Post back your result.
I was originally having it look for the last entry, but I tested the above
and it returned the correct answer, so this is much simpler.
- KC
 
Top