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

can someone help me with formula for excel?

Topic closed. 13 replies. Last post 8 years ago by jester001.

Page 1 of 1
PrintE-mailLink
Avatar

Philippines
Member #67927
December 8, 2008
67 Posts
Offline
Posted: July 9, 2009, 2:02 pm - IP Logged

hello sir/mam

can someone please help me for some excel formula. for example i have rows A1toA30 and i wanted to calculate the number of times that each number has appeared in the cell that i have selected. the numbers are from 0 to 9 and i just wanted to know how many times each number has appeared in the cells that i have mentioned. 

also is it possible to calculate in excel the number of times a certain number is followed by a certain number for example in cell A1toA30 there are numbers ranging from 0 to 9 and in A30 the number 8 appeared i just wanted to know how many times each number from 0 to 9 followed from the vells A1toA30.

ex. from A1toA30 we can calculate this

a30 = 8

0 = 1

1 = 5

2 = 7

3 = 5

4 = 0

etc...

tnx for anyone who will kindly reply to my post also sorry for my english it is not my native language. tnx again in advance. :)

    KnuckleHead's avatar - box

    United States
    Member #73037
    April 3, 2009
    147 Posts
    Offline
    Posted: July 9, 2009, 3:38 pm - IP Logged

    I believe what you need is something like:

    =COUNTIF(A1...A30,"=0")    >change the "0" to what ever number you want to count<

    That formula should be placed in each cell that you want the "counted" numbers to be in.

    0=how many 0's

    1=how many 1's

    2=how many 2's

    etc. thru 9

    You can change the "range" (A1...A30) that you want to count.

     

    Hope that's what you were looking for...

              The only DUMB question is the one question you DID NOT ask...

      Avatar

      Philippines
      Member #67927
      December 8, 2008
      67 Posts
      Offline
      Posted: July 9, 2009, 6:36 pm - IP Logged

      ok sir tnx alot. will try it out. :) tnx again sir. :)

        CARBOB's avatar - FL LOTTERY_LOGO.png
        ORLANDO, FLORIDA
        United States
        Member #4924
        June 3, 2004
        5982 Posts
        Offline
        Posted: July 9, 2009, 6:58 pm - IP Logged

        I believe what you need is something like:

        =COUNTIF(A1...A30,"=0")    >change the "0" to what ever number you want to count<

        That formula should be placed in each cell that you want the "counted" numbers to be in.

        0=how many 0's

        1=how many 1's

        2=how many 2's

        etc. thru 9

        You can change the "range" (A1...A30) that you want to count.

         

        Hope that's what you were looking for...

        If I understand what they are looking for a formula to count the number of times a digit has followed another digit. If so, this formula will work  =SUMPRODUCT(--($U$7:$U$17=$X7),(--($U$8:$U$18=Y$6)))

        Make sure to use Ctrl-Shift-Enter to enter the formula.

          Avatar

          Philippines
          Member #67927
          December 8, 2008
          67 Posts
          Offline
          Posted: July 10, 2009, 12:22 am - IP Logged

          tnx alot carbob. :) can this be done individually for example only the numbers in the a coulmn are sorted then seperate for the b coulmn and c column? so there will be three of them tnx agan sir. :)

            Avatar

            Philippines
            Member #67927
            December 8, 2008
            67 Posts
            Offline
            Posted: July 10, 2009, 12:54 am - IP Logged

            hello again sir carbob i just have a question again can it also be done automatically for example we just put a number on a certain row and then it automatically updates the numbes? lso can this be done individually like in pick 3 we have p1 p2 p3 i wanted to analyze each one of them and their following numbers tnx again sir. :)

              CARBOB's avatar - FL LOTTERY_LOGO.png
              ORLANDO, FLORIDA
              United States
              Member #4924
              June 3, 2004
              5982 Posts
              Offline
              Posted: July 10, 2009, 5:49 am - IP Logged

              hello again sir carbob i just have a question again can it also be done automatically for example we just put a number on a certain row and then it automatically updates the numbes? lso can this be done individually like in pick 3 we have p1 p2 p3 i wanted to analyze each one of them and their following numbers tnx again sir. :)

              It would take a macro to do that. The example I posted is for one column only. You created 2 more tables like the example, change the ranges, then you have all 3 positions. When you add a draw, do a a replace to current row.

                KnuckleHead's avatar - box

                United States
                Member #73037
                April 3, 2009
                147 Posts
                Offline
                Posted: July 10, 2009, 8:37 am - IP Logged

                Sorry, I misunderstood the question...

                          The only DUMB question is the one question you DID NOT ask...

                  Avatar

                  Philippines
                  Member #67927
                  December 8, 2008
                  67 Posts
                  Offline
                  Posted: July 10, 2009, 9:36 am - IP Logged

                  to knucklehead the one that you gave me worked. :) tnx alot.

                  carbob was teaching me the answer to my sexond question.

                  to carbob: ok tnx alot sir. will do just that.

                  tnx again to both of you for the help. :)

                    Avatar

                    Philippines
                    Member #67927
                    December 8, 2008
                    67 Posts
                    Offline
                    Posted: July 10, 2009, 12:15 pm - IP Logged

                    =SUMPRODUCT(--($B$2:$B$2556=$AB2),(--($B$3:$B$2557=$AC1)))

                     

                    hello carbob? is the formula right? im getting only zero's tnx again. btw do you know how to convert it to macro? tnx again sir. :)

                      Avatar

                      United States
                      Member #41846
                      June 23, 2006
                      460 Posts
                      Offline
                      Posted: July 10, 2009, 9:40 pm - IP Logged

                      =SUMPRODUCT(--($B$2:$B$2556=$AB2),(--($B$3:$B$2557=$AC1)))

                       

                      hello carbob? is the formula right? im getting only zero's tnx again. btw do you know how to convert it to macro? tnx again sir. :)

                      Did you remember to start with ctrl shift enter  to let excel know that it was an array formula?

                       

                      Macro    on tool bar goto tools macro record new macro  use default name or enter one,  select cell you want result displayed in, enter your formula.   stop macro  you now have a macro you can use by pressing alt f8 and selecting

                       

                      good luck

                        Avatar

                        Philippines
                        Member #67927
                        December 8, 2008
                        67 Posts
                        Offline
                        Posted: July 10, 2009, 11:26 pm - IP Logged

                        ok tnx alot sir will try it out. :) tnx again sir.

                          Avatar

                          Philippines
                          Member #67927
                          December 8, 2008
                          67 Posts
                          Offline
                          Posted: July 11, 2009, 12:04 am - IP Logged

                          hello again sir i just did what you told me what i get are all zero's. :(

                            Avatar

                            Philippines
                            Member #67927
                            December 8, 2008
                            67 Posts
                            Offline
                            Posted: July 11, 2009, 1:27 am - IP Logged

                            finally got it to work. :) tnx alot kind sirs for all the help. :)