Welcome Guest
Log In | Register )
You last visited January 21, 2017, 2:30 am
All times shown are
Eastern Time (GMT-5:00)

Need excel formula

Topic closed. 6 replies. Last post 11 years ago by hypersoniq.

Page 1 of 1
PrintE-mailLink
Sandy K's avatar - graphic pub.jpg
Clarkston, Michigan
United States
Member #76
January 6, 2002
8528 Posts
Offline
Posted: January 14, 2006, 8:49 am - IP Logged

I want to count how many times a digit fell......

Cells B2,B3,B4 are the individual digits cells

thru Row 8

preferable sorted with the most hit digits first..........

Hypersoniq or WSLS help!!!!!!!

The idea is to take 7 days of draws, which would be 42 digits and find out which ones hit the most in a sorted list.

Sandy

    lottaloot's avatar - AvatarZ56
    Redford/MI
    United States
    Member #3396
    January 18, 2004
    4867 Posts
    Offline
    Posted: January 14, 2006, 9:07 am - IP Logged
     P1P2P3
    026311
    124814
    236211
    32338
    43339
    544412
    648517
    780210
    85016
    932510

     

    I have my table set up like this.  Will get you by until someone else gives you something better. 

    Draws set up in cells D:F

    P1=D2:D43

    P2=E2:E43

    P3=F2:F43

    equals results

    Then grey cells starting at 0

    =sum(of P1:P3)

    From there you have to count the old fashioned way or use auto filter, etc. 

    L ttaL   T

      Sandy K's avatar - graphic pub.jpg
      Clarkston, Michigan
      United States
      Member #76
      January 6, 2002
      8528 Posts
      Offline
      Posted: January 14, 2006, 9:17 am - IP Logged

      thank you big time, LL.......have a great day.  Not really what I want but will play with it.....not interested in sums....just the count...I think it is a COUNTIF command or a COUNT command.

      In the gray area, why can't I just sort with the decending order AZ thingie on the tool bar?

      Sandy

        Sandy K's avatar - graphic pub.jpg
        Clarkston, Michigan
        United States
        Member #76
        January 6, 2002
        8528 Posts
        Offline
        Posted: January 14, 2006, 9:35 am - IP Logged

        thanks LL......but I just got the formula.

        Sandy

          hypersoniq's avatar - 8ball
          Pennsylvania
          United States
          Member #1340
          April 6, 2003
          2450 Posts
          Offline
          Posted: January 14, 2006, 12:09 pm - IP Logged

          was it a =countif() formula?

          I have yet to try, but I think the =rank() formula could help with the order...

          Playing more than one ticket per game is betting against yourself.

            Sandy K's avatar - graphic pub.jpg
            Clarkston, Michigan
            United States
            Member #76
            January 6, 2002
            8528 Posts
            Offline
            Posted: January 14, 2006, 12:18 pm - IP Logged

            yes hyper is was:

            =COUNTIF($B$2:$D$8,G2)

            what do you suggest?

            How would I do it with the =rank?

            the countif formula counts well but it doesn't sort largest hits at the top on down......

            Here is the Countif results:

            DigitsHits
            04
            12
            23
            34
            41
            52
            61
            71
            82
            91

            It counts but I wanted sorted so that the 0-3-2 is on top with 4 hits each and so on.  If I remember, I think the "rank" would do it but don't know how to do it.

            Really what I am trying to do is set up a "Power Trails" spreadsheet.....fiddling with the amount of draws inputted right now. 

            Sandy

              hypersoniq's avatar - 8ball
              Pennsylvania
              United States
              Member #1340
              April 6, 2003
              2450 Posts
              Offline
              Posted: January 14, 2006, 8:28 pm - IP Logged

              rank would only give a number value to the hits count

              there are some special uses of rank, however... instructions here...

              http://www.cpearson.com/excel/rank.htm

              could be a tricky formula is needed, I'm still brushing up on formulas (creating your own) so I'll have to get back to you on this one... manually, you could select the 2 columns, click on SORT and pick the 2nd of the 2 columns in descending order, this would put the high counts at the top with their corresponding digits (but only if both ranges are selected)... that's how I do quick imports from state lottery games, copy their descended order list and re-sort it by date ascending (so I have newest draws at the bottom)

              Playing more than one ticket per game is betting against yourself.