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

EXCEL Experts

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

Page 1 of 1
PrintE-mailLink
empassioned1's avatar - abns7
Saint Louis, Missouri
United States
Member #17000
June 9, 2005
46 Posts
Offline
Posted: June 29, 2005, 12:21 am - IP Logged

Can someone point me to or help me with an excel formula or how to perhaps an if then statement.

What I am trying to do is add a list of three numbers in three columns, with each sum I ONLY want the ones place and nothing more.  Since the largest number would be 27 (9+9+9)...I'm tossing out 10 or 20's place.

for example: if I added 6+4+2=12 I only want the 2, not the 10 infront of it.

Make sense?  So in Excel I would have a sum of say: A3+B2+C1  Now what I need to know is if that total is over 10 or 20 how to get the cell to show only the ones place

This brings me to another potential problem too.  IF I get it to show only the ones place, that number is used in the next sum, will it take the number shown or will it attempt to use the original sum of the cell?

I'm trying to save myself a lot of paper work, but if I have to write it all out by hand I will.  I have already figured out the base sums for each week (looking at box patterns based on previous week's draw)

Thanks,

C.K.

If the eyes are the windows to the soul, let me see you without the shades drawn

 

What's a Hades Bunny?  Well let's just say you don't wanna be jumpin' over MY back fence.  But hey, you ain't afraid a lil white rabbit now are ya?

    GRRowl's avatar - Lottery-027.jpg
    Minnesota
    United States
    Member #1367
    April 12, 2003
    37 Posts
    Offline
    Posted: June 29, 2005, 12:45 am - IP Logged
    Empassioned1, you can try this formula.
    =RIGHT((A1+B1+C1),1)
    That should leave you with just the ones position.

    I know the voices aren't real, but they sure have some good ideas.

      empassioned1's avatar - abns7
      Saint Louis, Missouri
      United States
      Member #17000
      June 9, 2005
      46 Posts
      Offline
      Posted: June 29, 2005, 3:00 am - IP Logged

      Hmmm, tried that, the chart I had suddenly filled each cell with "#value"

       

      I think for now, since the 1's digit are consistant (nothing carries over like the 10's 100's or higher) I can plug the formula and simply make sure I am only pulling the 1's position for each sum in each cell.

      It's nearly 2 am here, so perhaps I'll try this method again tomorrow with a clean Excel document and if it works (on more sleep) I'll redo the chart from the top.

      Thanks for the help by the way!

      C.K.

      If the eyes are the windows to the soul, let me see you without the shades drawn

       

      What's a Hades Bunny?  Well let's just say you don't wanna be jumpin' over MY back fence.  But hey, you ain't afraid a lil white rabbit now are ya?

        JADELottery's avatar - MeAtWork 03.PNG
        The Quantum Master
        West Concord, MN
        United States
        Member #21
        December 7, 2001
        3675 Posts
        Offline
        Posted: June 29, 2005, 3:28 am - IP Logged

        Try this:

        =(A1+B1+C1)-10*(INT((A1+B1+C1)/10))

        Presented 'AS IS' and for Entertainment Purposes Only.
        Any gain or loss is your responsibility.
        Use at your own risk.

        Order is a Subset of Chaos
        Knowledge is Beyond Belief
        Wisdom is Not Censored
        Douglas Paul Smallish
        Jehocifer

          Todd's avatar - Cylon 2.gif
          Chief Bottle Washer
          New Jersey
          United States
          Member #1
          May 31, 2000
          23273 Posts
          Online
          Posted: June 29, 2005, 6:57 am - IP Logged

          The easiest was to strip the 10's column is to use a MOD function. 

          =MOD(A1+B1+C1, 10)

           

          Check the State Lottery Report Card
          What grade did your lottery earn?

           

          Sign the Petition for True Lottery Drawings
          Help eliminate computerized drawings!

            winsumloosesum's avatar - Lottery-060.jpg
            Pennsylvania
            United States
            Member #2218
            September 1, 2003
            5387 Posts
            Offline
            Posted: June 29, 2005, 9:53 am - IP Logged

            If your sum total is in E3

            =IF(E3<10,E3,IF(E3<20,E3-10,E3-20))

            wsls 

              lottaloot's avatar - AvatarZ56
              Redford/MI
              United States
              Member #3396
              January 18, 2004
              4867 Posts
              Offline
              Posted: June 29, 2005, 12:11 pm - IP Logged

              Looking for a formula that will return a result of how many days out a pair is.

              Pair's location start in (V2) and in in V46.  Am I to use lookup? 

              Any help is appreciated!!

               

                winsumloosesum's avatar - Lottery-060.jpg
                Pennsylvania
                United States
                Member #2218
                September 1, 2003
                5387 Posts
                Offline
                Posted: June 29, 2005, 2:32 pm - IP Logged

                lottaloot,

                One idea is to add another tab to your excel worksheet and name it Pairs.

                In cell a1 through cell a100 would be all your pairs 00 through 99.

                The Pick 3 history drawings, I use is named Pick3. a5 contains my date, b5 is my 3 digit number drawn, and c5 is my Front Pair.

                In cell b1 enter the following formula in the Pairs worksheet:

                =MATCH($A1,INDIRECT("Pick3!c5:c1000"),0)-1

                This will give you the number of drawings since this Front Pair hit. Just repeat for Back Pair and Side Pairs.

                1000 draws should give you enough history for pairs.

                Hope thsi helps!!

                wsls

                  Todd's avatar - Cylon 2.gif
                  Chief Bottle Washer
                  New Jersey
                  United States
                  Member #1
                  May 31, 2000
                  23273 Posts
                  Online
                  Posted: June 29, 2005, 4:25 pm - IP Logged

                  If your sum total is in E3

                  =IF(E3<10,E3,IF(E3<20,E3-10,E3-20))

                  wsls 

                  Unfortunately, a limited forumla such as that will crash when the parameters are out of its range (for example, greater than 40).  You are much better off using a formula such as the MOD() that I recommended above, since it will not crash like that, no matter what number you use.  Always look for that which is (a) simplest, and (b) most flexible

                   

                  Check the State Lottery Report Card
                  What grade did your lottery earn?

                   

                  Sign the Petition for True Lottery Drawings
                  Help eliminate computerized drawings!

                    winsumloosesum's avatar - Lottery-060.jpg
                    Pennsylvania
                    United States
                    Member #2218
                    September 1, 2003
                    5387 Posts
                    Offline
                    Posted: June 29, 2005, 4:59 pm - IP Logged

                    I thought empassioned was just adding 3 columns for 3 digits.

                    The largest number would be 27. (9 + 9 + 9 =27)

                    That's what I thought the request was for.

                    My mistake.

                    wsls 

                      empassioned1's avatar - abns7
                      Saint Louis, Missouri
                      United States
                      Member #17000
                      June 9, 2005
                      46 Posts
                      Offline
                      Posted: June 29, 2005, 6:08 pm - IP Logged

                      OK! Now'm on the downhill slope!  The =MOD is the simplest, although the IF formula WSLS gave me works as well, just needed some extra cells to make the chart work.

                      First off, Thank YOU guys and gals!  I always appreciate the help, and as I get this initial calculation work done, I'll post more in a new thread or update the original thread where this all started with Atomic Dog's "Union Jack" method. 

                      Thus far, I've had a box or straight hit every single week.  Now I'm doing the math to see what patterns evolve over close to a year's draws using the "computerized" draws rather than going back before when the draws were ball derived.

                      Thanks again for the help!!

                      This is getting to be just too cool!

                      C.K.

                      If the eyes are the windows to the soul, let me see you without the shades drawn

                       

                      What's a Hades Bunny?  Well let's just say you don't wanna be jumpin' over MY back fence.  But hey, you ain't afraid a lil white rabbit now are ya?

                        Avatar
                        New Member
                        Milwaukee
                        United States
                        Member #18118
                        July 5, 2005
                        1 Posts
                        Offline
                        Posted: July 5, 2005, 2:55 pm - IP Logged

                        I've used this in a spread sheet and have tested it. What it will not do is catch values over 27.

                        This should do what you want it to ' =IF(A1> 19,A1-20,IF(A1>9,A1-10,A1)) '