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

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

OTOH, why bother?

In article . net,
"Bob Smith" wrote:

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?

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

"JE McGimpsey" wrote in message
...
One suggestion would be to use Data/Text to Columns to separate the
numbers (choose Delimited/Space)

OTOH, why bother?

In article . net,
"Bob Smith" wrote:

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?

"JE McGimpsey" wrote in message
...
One suggestion would be to use Data/Text to Columns to separate the
numbers (choose Delimited/Space)

OTOH, why bother?

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

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

"Bob Smith" wrote in message
ink.net...
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