How can I do this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Just Me - 31 Jan 2007 21:06 GMT

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

Reply to this Message

John Bundy - 31 Jan 2007 21:33 GMT

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

Signature

-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.

> 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
[quoted text clipped - 5 lines]
>
> Thanks in advance

Reply to this Message

KC Rippstein - 31 Jan 2007 21:39 GMT

=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

Reply to this Message

Just Me - 31 Jan 2007 21:54 GMT

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

> =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
[quoted text clipped - 10 lines]
>>
>> Thanks in advance

Reply to this Message

KC Rippstein - 31 Jan 2007 22:05 GMT

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

>I did as you say but I get #VALUE !
> Column A is the date b- h contain the numbers if that makes a difference
[quoted text clipped - 13 lines]
>>>
>>> Thanks in advance

Reply to this Message

Just Me - 31 Jan 2007 22:34 GMT

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

>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
[quoted text clipped - 21 lines]
>>>>
>>>> Thanks in advance

Reply to this Message

KC Rippstein - 31 Jan 2007 22:45 GMT

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

> 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
[quoted text clipped - 31 lines]
>>>>>
>>>>> Thanks in advance

Reply to this Message

Just Me - 01 Feb 2007 00:04 GMT

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

> In B1, =MODE(B$5:B$65536) and then use the fill handle to drag that
> formula across to G1. Post back your result.
[quoted text clipped - 38 lines]
>>>>>>
>>>>>> Thanks in advance

Reply to this Message

KC Rippstein - 01 Feb 2007 23:16 GMT

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
[quoted text clipped - 46 lines]
>>>>>>>
>>>>>>> Thanks in advance

Reply to this Message