Welcome Guest
You last visited January 21, 2017, 4:15 am
All times shown are
Eastern Time (GMT-5:00)

# Xcel formula question

Topic closed. 6 replies. Last post 10 years ago by Griz.

 Page 1 of 1
New Member

United States
Member #49886
February 18, 2007
4 Posts
Offline
 Posted: June 30, 2007, 11:07 pm - IP Logged

Hey everyone,

Need some help from Excel experts out there...

here's my problem:

Say I have the results from a lottery, 1, 5, 22, 26, 44,  mega ball 7

Now, say for example, I have number group 1-18 as group 1, 19-37 as group 2 and

38 - 56 as group 3.  I need forumlas which would filter out the lotto results, 1 is group1, 5 is group 1, 22 group 2, 26 group2, 44 group3.

Also looking for a nother formula which would then total up the groups, and return the following:

2 in group 1, 2 in group 2 and 1 in group 3.

Can anyone out there comeup with formulas to do this?

Many thanks in advance for any help.

Griz

in

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5972 Posts
Online
 Posted: July 1, 2007, 7:58 am - IP Logged

Hey everyone,

Need some help from Excel experts out there...

here's my problem:

Say I have the results from a lottery, 1, 5, 22, 26, 44,  mega ball 7

Now, say for example, I have number group 1-18 as group 1, 19-37 as group 2 and

38 - 56 as group 3.  I need forumlas which would filter out the lotto results, 1 is group1, 5 is group 1, 22 group 2, 26 group2, 44 group3.

Also looking for a nother formula which would then total up the groups, and return the following:

2 in group 1, 2 in group 2 and 1 in group 3.

Can anyone out there comeup with formulas to do this?

Many thanks in advance for any help.

Griz

in

Is this what you're looking for?

 1 5 22 26 44 221

Enter the formula as an Array, Ctrl-Shift-Enter. Change the bold range to your sheet!

=(COUNTIF(\$W18:\$AA18,"<19")&COUNTIF(\$W18:\$AA18,"<38")-COUNTIF(\$W18:\$AA18,"<19")&COUNTIF(\$W18:\$AA18,">37"))+0

Hope this helps!!

New Member

United States
Member #49886
February 18, 2007
4 Posts
Offline
 Posted: July 1, 2007, 9:26 am - IP Logged

CARBOB,

Thanks for your reply... while it's not exactly what I was looking for, I do like what your formula does and will put it to use...

Here's what I was looking for originally,

the lotto results enterd into cells A1 to E1 as below:

1   5    22    26    44

Then formula's to return either G1, G2 or G3 in cells A2 to E2 based on what

group the number in corresponding number above is in

G1 is 1-18, G2 is 19-37 and G3 is 38-56

Also interested in a formula which would return either A "E" if the number is Even or "O" if

the number is Odd.

Griz

United States
Member #33295
February 19, 2006
699 Posts
Offline
 Posted: July 1, 2007, 5:16 pm - IP Logged

Enter  =MATCH(A1,{0;19;38},1)  in Cell A2 and copy over to G2.

That will give you this

 1 5 22 26 44 1 1 2 2 3

Enter  =IF(ISODD(A1),"O","E")  in Cell A3 and copy over to G3

and you will get this

 1 5 22 26 44 1 1 2 2 3 O O E E E

If you want to combine them in A4

enter  =CONCATENATE(A2,A3)  in A4 and copy over to G4

and you will get this

 1 5 22 26 44 1 1 2 2 3 O O E E E 1O 1O 2E 2E 3E

Does this look like what you want?

Bud

Isabel, you are going to feel very silly when this turns out to be make-believe.

United States
Member #33295
February 19, 2006
699 Posts
Offline
 Posted: July 1, 2007, 5:34 pm - IP Logged

"Also looking for a nother formula which would then total up the groups, and return the following:

2 in group 1, 2 in group 2 and 1 in group 3."

In Cell L1 enter 1

In Cell M1 enter 2

In Cell N1 enter 3

In Cell G1 enter Group1,  In Cell G2 enter  =COUNTIF(A2:E2,\$L\$1)

In Cell H1 enter Group 2,  In Cell H2 enter  =COUNTIF(A2:E2,\$M\$1)

In Cell I1 enter Group 3,  In Cell I2 enter  =COUNTIF(A2:E2,\$N\$1)

You should have this now

 1 5 22 26 44 Group1 Group 2 Group 3 1 2 3 1 1 2 2 3 2 2 1 O O E E E 1O 1O 2E 2E 3E

Bud

Isabel, you are going to feel very silly when this turns out to be make-believe.

United States
Member #33295
February 19, 2006
699 Posts
Offline
 Posted: July 1, 2007, 8:39 pm - IP Logged

I guess I should mention that once the formulas are set  up, you can highlight and drag the cells where ever you want them on your spreadsheet

Bud

Isabel, you are going to feel very silly when this turns out to be make-believe.

New Member

United States
Member #49886
February 18, 2007
4 Posts
Offline
 Posted: July 1, 2007, 8:51 pm - IP Logged

Bud,

Yup, your formulas did the trick... many thanks for your help.. will let you know when (and I most likey will - I suck at these formulas) need assistance again.  tks again

Griz

 Page 1 of 1