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

Looking for Excel Formula

Topic closed. 19 replies. Last post 1 year ago by AllenB.

Page 1 of 2
PrintE-mailLink
CARBOB's avatar - FL LOTTERY_LOGO.png
ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5963 Posts
Offline
Posted: August 20, 2015, 10:02 am - IP Logged

Need an Excel formula that will measure skips using the <= and >= commands. Anyone have one and would like to share, I would be much obliged.

    CARBOB's avatar - FL LOTTERY_LOGO.png
    ORLANDO, FLORIDA
    United States
    Member #4924
    June 3, 2004
    5963 Posts
    Offline
    Posted: August 20, 2015, 10:38 am - IP Logged

    Need an Excel formula that will measure skips using the <= and >= commands. Anyone have one and would like to share, I would be much obliged.

    This is the formula, I am hoping someone can explain the syntax. Keep getting formula contains an error.

    =IF(COLUMNS($R10:R10)<=COUNTIFS($B$2:$B$5316,">=1",COUNTIFS($B$2:$B$5316,"<=50",COUNTIFS($B$2:$B$5316>=1,COUNTIFS($B$2:$B$5316<=50,ROW($B$2:$B$5316)-ROW($B$2)),COLUMNS($R10:R10))

      Raven62's avatar - binary
      New Jersey
      United States
      Member #17843
      June 28, 2005
      51064 Posts
      Offline
      Posted: August 20, 2015, 11:25 am - IP Logged

      This is the formula, I am hoping someone can explain the syntax. Keep getting formula contains an error.

      =IF(COLUMNS($R10:R10)<=COUNTIFS($B$2:$B$5316,">=1",COUNTIFS($B$2:$B$5316,"<=50",COUNTIFS($B$2:$B$5316>=1,COUNTIFS($B$2:$B$5316<=50,ROW($B$2:$B$5316)-ROW($B$2)),COLUMNS($R10:R10))

      =IF(COLUMNS($R10:R10)<=COUNTIFS($B$2:$B$5316,">=1",COUNTIFS($B$2:$B$5316,"<=50",COUNTIFS($B$2:$B$5316,">=1",COUNTIFS($B$2:$B$5316,"<=50",ROW($B$2:$B$5316)-ROW($B$2)),COLUMNS($R10:R10))

      Missing ,">=1"

      A mind once stretched by a new idea never returns to its original dimensions!

        CARBOB's avatar - FL LOTTERY_LOGO.png
        ORLANDO, FLORIDA
        United States
        Member #4924
        June 3, 2004
        5963 Posts
        Offline
        Posted: August 20, 2015, 11:37 am - IP Logged

        Thanks for responding, where is that entered into the formula??

          Raven62's avatar - binary
          New Jersey
          United States
          Member #17843
          June 28, 2005
          51064 Posts
          Offline
          Posted: August 20, 2015, 1:09 pm - IP Logged

          Thanks for responding, where is that entered into the formula??

          Compare the formula you posted with the copy & pasted formula posted with corrections applied.

          A mind once stretched by a new idea never returns to its original dimensions!

            CARBOB's avatar - FL LOTTERY_LOGO.png
            ORLANDO, FLORIDA
            United States
            Member #4924
            June 3, 2004
            5963 Posts
            Offline
            Posted: August 20, 2015, 1:16 pm - IP Logged

            Compare the formula you posted with the copy & pasted formula posted with corrections applied.

            Still getting error message.

            https://app.box.com/s/tbonkt9m0jxv2q6hed2zfj8domuygz9j

              Raven62's avatar - binary
              New Jersey
              United States
              Member #17843
              June 28, 2005
              51064 Posts
              Offline
              Posted: August 20, 2015, 1:49 pm - IP Logged

              =IF(COLUMNS($R10:R10)<=COUNTIFS($B$2:$B$5316,">=1",COUNTIFS($B$2:$B$5316,"<=50",COUNTIFS($B$2:$B$5316,">=1",COUNTIFS($B$2:$B$5316,"<=50",ROW($B$2:$B$5316)-ROW($B$2)),COLUMNS($R10:R10))

              Missing ,">=1"

              Oops missing )'s

              =IF(COLUMNS($R10:R10)<=COUNTIFS($B$2:$B$5316,">=1"),COUNTIFS($B$2:$B$5316,"<=50"),COUNTIFS($B$2:$B$5316,">=1"),COUNTIFS($B$2:$B$5316,"<=50"),ROW($B$2:$B$5316)-ROW($B$2)),COLUMNS($R10:R10))

              Was it your intention to include an AND or an OR?

              A mind once stretched by a new idea never returns to its original dimensions!

                CARBOB's avatar - FL LOTTERY_LOGO.png
                ORLANDO, FLORIDA
                United States
                Member #4924
                June 3, 2004
                5963 Posts
                Offline
                Posted: August 20, 2015, 3:07 pm - IP Logged

                No, the formula still gives an error message.

                =IF(COLUMNS($R10:R10)<=COUNTIFS($B$2:$B$5316,">=1"),COUNTIFS($B$2:$B$5316,"<=50"),COUNTIFS($B$2:$B$5316,">=1"),COUNTIFS($B$2:$B$5316,"<=50"),ROW($B$2:$B$5316)-ROW($B$2)),COLUMNS($R10:R10))

                  winsumloosesum's avatar - Lottery-060.jpg
                  Pennsylvania
                  United States
                  Member #2218
                  September 1, 2003
                  5396 Posts
                  Offline
                  Posted: August 20, 2015, 7:27 pm - IP Logged

                  Bob,

                  Not sure is this is what you wanted:

                  =MATCH(1,--(B2:B5136>=1)*--(B2:B5136<=50),0)  Ctrl Shift Enter

                    sully16's avatar - sharan
                    Ringleader
                    Michigan
                    United States
                    Member #81740
                    October 28, 2009
                    41988 Posts
                    Offline
                    Posted: August 21, 2015, 3:13 am - IP Logged

                    Need an Excel formula that will measure skips using the <= and >= commands. Anyone have one and would like to share, I would be much obliged.

                    Wish I even knew how to use it.

                     HyperBe Happy.

                      CARBOB's avatar - FL LOTTERY_LOGO.png
                      ORLANDO, FLORIDA
                      United States
                      Member #4924
                      June 3, 2004
                      5963 Posts
                      Offline
                      Posted: August 21, 2015, 3:42 am - IP Logged

                      Bob,

                      Not sure is this is what you wanted:

                      =MATCH(1,--(B2:B5136>=1)*--(B2:B5136<=50),0)  Ctrl Shift Enter

                      1726
                      956
                      748
                      48

                      Thanks Steve, that works, with one exception. Next question, just to clarify. The values listed are in descending order, current to oldest. I count 3 draws as being current skip, right or wrong? Or is it 4, which is what your formula returns. If my count is right, I can compensate with a -1. Just want to be sure of the count.

                      It only works for one instance. It doesn't work if I change the range from >=50 & <=100, returns wrong value.

                        CARBOB's avatar - FL LOTTERY_LOGO.png
                        ORLANDO, FLORIDA
                        United States
                        Member #4924
                        June 3, 2004
                        5963 Posts
                        Offline
                        Posted: August 21, 2015, 7:10 am - IP Logged

                        You can forget this thread, figured out a different way of doing the same thing. Thanks to Raven62 and Steve, much appreciated.

                          Raven62's avatar - binary
                          New Jersey
                          United States
                          Member #17843
                          June 28, 2005
                          51064 Posts
                          Offline
                          Posted: August 21, 2015, 8:26 am - IP Logged

                          Wish I even knew how to use it.

                          You can: Teach Yourself, Take a DVD Course, Attend a Class at the Library or Local School where you live.

                          A mind once stretched by a new idea never returns to its original dimensions!

                            Raven62's avatar - binary
                            New Jersey
                            United States
                            Member #17843
                            June 28, 2005
                            51064 Posts
                            Offline
                            Posted: August 21, 2015, 8:30 am - IP Logged

                            You can forget this thread, figured out a different way of doing the same thing. Thanks to Raven62 and Steve, much appreciated.

                            Your Welcome Carbob!

                            If you're willing to share the alternate way of doing the same thing I'd like to hear about it.

                            A mind once stretched by a new idea never returns to its original dimensions!

                              CARBOB's avatar - FL LOTTERY_LOGO.png
                              ORLANDO, FLORIDA
                              United States
                              Member #4924
                              June 3, 2004
                              5963 Posts
                              Offline
                              Posted: August 21, 2015, 12:48 pm - IP Logged

                              =SUBSTITUTE(ADDRESS(1,(D3-1)/50+1,4),"1","")

                              This formula puts everything in a group of 50.