How can I do this?

[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

 

[Guest]

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,1)
=COUNTIF(B,2)
=COUNTIF(B,3)
etc then you have a running total

 

[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

 

[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

 

[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

 

[Just Me]

Sorry I am such a dunce at this but I can't get it to work. I'm sure I
explained it wrong so I tool a screenshot of my worksheet to show you
exactly how it's set up.

Hope this helps you help me.......lol


www.eaglepi.com/images/powerball.jpg


Thanks again

 

[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

 

[Just Me]

Here are the results, So to make sure I am getting what I want, the numbers
across the top in each column is the number that appears in that column the
most times right?

www.eaglepi.com/images/powerball.jpg

Thanks again

 

[KC Rippstein]

Yep, that's it!

Here are the results, So to make sure I am getting what I want, the
numbers across the top in each column is the number that appears in that
column the most times right?

www.eaglepi.com/images/powerball.jpg

Thanks again