Welcome Guest
Log In | Register )
You last visited December 8, 2016, 4:55 pm
All times shown are
Eastern Time (GMT-5:00)

Excel Help!!

Topic closed. 11 replies. Last post 11 years ago by CARBOB.

Page 1 of 1
PrintE-mailLink
winsumloosesum's avatar - Lottery-060.jpg
Pennsylvania
United States
Member #2218
September 1, 2003
5387 Posts
Offline
Posted: September 16, 2005, 5:09 pm - IP Logged

Calling all Excel Guru's!!!

Looking for a formula for tracking last 3 skips from a Pick 3 drawing history.

For example:

Say I have a drawing history and I whant to know when the digit #3 hit the last 3 times in position 1.

So my chart would look something like this:

Pick 3

Position 1    Hit#1    Hit#2    Hit#3

0                (these would be the drawing number(s) the hits occured

1

2

3

4

5

6

7

8

9

Thanks in advance for the help!!!

wsls

 

    lottaloot's avatar - AvatarZ56
    Redford/MI
    United States
    Member #3396
    January 18, 2004
    4867 Posts
    Offline
    Posted: September 16, 2005, 7:19 pm - IP Logged

    I would like that formula as well if there is such a formula. 

    For the time being, I simply use a conditional format in order to find what I am looking for in whatever position (or all 3) that I need it for. 

     

      BobP's avatar - bobp avatar.png
      Dump Water Florida
      United States
      Member #380
      June 5, 2002
      3104 Posts
      Offline
      Posted: September 17, 2005, 3:47 am - IP Logged

      What's Excel mean when you get an error "not a valid interger"  after typing in your numbers?

        LANTERN's avatar - kilroy 28_173_reasonably_small.jpg
        Tx
        United States
        Member #4570
        May 4, 2004
        5180 Posts
        Offline
        Posted: September 17, 2005, 10:08 am - IP Logged

        While I don't know anything about Excel myself maybe you can try asking here:

        http://groups.google.com/groups?hl=en&lr=&sa=X&oi=groupst&q=is+not+a+valid+integer+value+excel

        And or download this:

        http://www.thomsonib.com/downloads/troubleshooting_guide.xls

        Good luck.

        BibleOnline  ParishesOnline  ChristianRadioOnline   MassOnline   Mass

        "Ten measures of beauty descended to the world, nine were taken by Jerusalem."

          lottaloot's avatar - AvatarZ56
          Redford/MI
          United States
          Member #3396
          January 18, 2004
          4867 Posts
          Offline
          Posted: September 18, 2005, 2:34 pm - IP Logged

          I need a formula that will allow me to track 1, 2, or 3 digit returns.  Does anyone have such a formula that they are willing to share? 

            SirMetro's avatar - center
            East of Atlanta
            United States
            Member #6191
            August 11, 2004
            1389 Posts
            Offline
            Posted: September 19, 2005, 3:04 pm - IP Logged

            What's Excel mean when you get an error "not a valid interger"  after typing in your numbers?

            This is known to occur when one either types in a non-numeric character (a-z, and any of the other symbols). It can also occur when you hit the period by mistake. Because the program is attempting to make a literal translation, a non-whole number (n.0001 thru n.9999, n=any number), it sees a variable ( I forget the exact syntax). Remember, an interger is a whole number with no decimals.

            Hope this helps.

            BTW, a workaround I use to avoid that error is to multiply that cell value with a 1 to insure it converts the data to a number value. Of course, this only works in formulas. If you are programming in VBA, you will have to do a hard conversion.

            Hope this helps,

            Sir Metro

              SirMetro's avatar - center
              East of Atlanta
              United States
              Member #6191
              August 11, 2004
              1389 Posts
              Offline
              Posted: September 19, 2005, 3:10 pm - IP Logged

              I need a formula that will allow me to track 1, 2, or 3 digit returns.  Does anyone have such a formula that they are willing to share? 

              Consider this as an idea (one that can be expanded upon and compounded if you pay attention the to parenthesis's that will be required)

              if the data in Cell A1 = 200-90 = 110, then

              =((LEN(TEXT(A1,"###")))) <---converts the number to a string and then looks to see how many spaces that string takes up. The ### is telling the computer that the data is in number format

              the above will give you 3 digit long.

              Perhaps this is what you are looking for?

              Sir Metro

                lottaloot's avatar - AvatarZ56
                Redford/MI
                United States
                Member #3396
                January 18, 2004
                4867 Posts
                Offline
                Posted: September 19, 2005, 3:40 pm - IP Logged

                Thanks SirMetro Big Smile

                I will give it a try & let you know if it's what I'm looking for & how it works. 

                  CARBOB's avatar - FL LOTTERY_LOGO.png
                  ORLANDO, FLORIDA
                  United States
                  Member #4924
                  June 3, 2004
                  5901 Posts
                  Online
                  Posted: September 19, 2005, 4:19 pm - IP Logged

                  This formula tracks skips.   

                  =IF(ISNUMBER(MATCH(W17,$K$7:$K$3919,0)),$A$7-(INDEX($A$7:$A$2051,MATCH(W17,$K$7:$K$3919,0))),"")

                   

                  this formula tracks number of digits that carried. It's rather long.

                  =MIN(COUNTIF(G10:I10,G10),COUNTIF(G11:I11,G10))/COUNTIF(G10:I10,G10)+MIN(COUNTIF(G10:I10,H10),COUNTIF(G11:I11,H10))/COUNTIF(G10:I10,H10)+MIN(COUNTIF(G10:I10,I10),COUNTIF(G11:I11,I10))/COUNTIF(G10:I10,I10)

                  Carbob

                    lottaloot's avatar - AvatarZ56
                    Redford/MI
                    United States
                    Member #3396
                    January 18, 2004
                    4867 Posts
                    Offline
                    Posted: September 19, 2005, 4:25 pm - IP Logged

                    Thanks Carbob--I like Long  Big Smile (formulas that is)

                      lottaloot's avatar - AvatarZ56
                      Redford/MI
                      United States
                      Member #3396
                      January 18, 2004
                      4867 Posts
                      Offline
                      Posted: September 19, 2005, 4:33 pm - IP Logged

                      This formula tracks skips.   

                      =IF(ISNUMBER(MATCH(W17,$K$7:$K$3919,0)),$A$7-(INDEX($A$7:$A$2051,MATCH(W17,$K$7:$K$3919,0))),"")

                       

                      this formula tracks number of digits that carried. It's rather long.

                      =MIN(COUNTIF(G10:I10,G10),COUNTIF(G11:I11,G10))/COUNTIF(G10:I10,G10)+MIN(COUNTIF(G10:I10,H10),COUNTIF(G11:I11,H10))/COUNTIF(G10:I10,H10)+MIN(COUNTIF(G10:I10,I10),COUNTIF(G11:I11,I10))/COUNTIF(G10:I10,I10)

                      Carbob

                      OK.  I am trying to think this one thru. 

                      I take it that the number that I am searching for would be in W17. 

                      K7 thru K3919 would be the ranges of my search & the 0 would be an exact match

                      so would A7 be the date? 

                      Am I correct in my thinking? 

                      I have to do this process so that I can change the formula to fit my spreadsheet (as I don't know where your draws start in yours) 

                        CARBOB's avatar - FL LOTTERY_LOGO.png
                        ORLANDO, FLORIDA
                        United States
                        Member #4924
                        June 3, 2004
                        5901 Posts
                        Online
                        Posted: September 19, 2005, 6:48 pm - IP Logged

                        This formula tracks skips.   

                        =IF(ISNUMBER(MATCH(W17,$K$7:$K$3919,0)),$A$7-(INDEX($A$7:$A$2051,MATCH(W17,$K$7:$K$3919,0))),"")

                         

                        this formula tracks number of digits that carried. It's rather long.

                        =MIN(COUNTIF(G10:I10,G10),COUNTIF(G11:I11,G10))/COUNTIF(G10:I10,G10)+MIN(COUNTIF(G10:I10,H10),COUNTIF(G11:I11,H10))/COUNTIF(G10:I10,H10)+MIN(COUNTIF(G10:I10,I10),COUNTIF(G11:I11,I10))/COUNTIF(G10:I10,I10)

                        Carbob

                        OK.  I am trying to think this one thru. 

                        I take it that the number that I am searching for would be in W17. 

                        K7 thru K3919 would be the ranges of my search & the 0 would be an exact match

                        so would A7 be the date? 

                        Am I correct in my thinking? 

                        I have to do this process so that I can change the formula to fit my spreadsheet (as I don't know where your draws start in yours) 

                        W17 (0)X17(1),Y17(2),Z17(3) is whatever you want to track, Ex 0,1,2,3 numbers carried.  K is col where I have the second formula stored which tells you how many digits carried. A is the date col.

                        You can PM with any questions.

                        Carbob