Suggestions on formula to track powerball numbers?

B

Bob Smith

Hi folks,

As a hobby, I've been tracking PB numbers picked over the past couple of
years. I have the numbers listed in each cell in three columns in this
format.
10 25 35 38 41 PB 23 (last night's pick). Am trying to pick out each
separate number with this formula, for each number between 1-55. To find 01
=
=COUNTA($B$1:$B$63,$F$1:$F$63,$J$1:$J$63,1)-11-21-31-41-51. This excludes
all other numbers ending with a 1, except for 01.

Only problem is that I can't separate out the PB number pick if that number
is one Tried numerous variations of formulas, changing how the PB number
shows ect. I realize I should have dedicated a separate column for the PB #,
but it's too late now, with over 160 sets of numbers already typed in.

Any ideas on how to complete this formula? Or, is there a better argument to
use?

TIA,

Bob
 
J

JE McGimpsey

One suggestion would be to use Data/Text to Columns to separate the
numbers (choose Delimited/Space)

OTOH, why bother?
 
B

Bob Smith

Thanks for the suggestion. As for why bother, like I said, it's just a
hobby. Am just curious which numbers come up the most, that's all ...

Bob
 
B

Bob Smith

JE McGimpsey said:
One suggestion would be to use Data/Text to Columns to separate the
numbers (choose Delimited/Space)

OTOH, why bother?
Click to expand...

By the by, that suggestion doesn't work, as it only picks up the first 3
sets of numbers, whether I use delimited or fixed.

Bob
 
B

Biff

Hi!

That's not a good way to enter your data. You should put one number in one
cell.

If one cell contains this string:

10 25 35 38 41 PB 23

And the format is consistent throughout such that single digit numbers are
padded with a leading zero:

01 09 35 38 41 PB 23

To count the number of occurences of the regular numbers, 25 for example:

=SUMPRODUCT(--ISNUMBER(FIND("25",LEFT(A1:A100,14))))

To count the number of occurences of the power ball, 01 for example:

=SUMPRODUCT(--ISNUMBER(FIND("01",RIGHT(A1:A100,2))))

Biff
 
Top