Welcome Guest
Log In | Register )
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
PrintE-mailLink
Avatar
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

    CARBOB's avatar - FL LOTTERY_LOGO.png
    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?

     

    15222644221

     

     

     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!!

      Avatar
      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. 

       Thanks again for your help,

      Griz

        Avatar

        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

         

        15222644
        11223

         

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

        and you will get this

         

        15222644
        11223
        OOEEE

         

        If you want to combine them in A4

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

        and you will get this

         

        15222644
        11223
        OOEEE
        1O1O2E2E3E

         

        Does this look like what you want?

        Bud

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

          Avatar

          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

           

          15222644Group1Group 2Group 3123
          11223221
          OOEEE
          1O1O2E2E3E

           

          Bud

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

            Avatar

            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.

              Avatar
              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