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

Spreadsheet Formula Needed

Topic closed. 13 replies. Last post 9 years ago by Rocket 455.

Page 1 of 1
PrintE-mailLink
Rocket 455's avatar - rocket
Bellevue, KY
United States
Member #53340
July 4, 2007
2622 Posts
Offline
Posted: July 28, 2007, 11:48 am - IP Logged

 I have been trying to figure out how to do this on my own but can't. What I need is a formula that will take any three digit number  input to A1 and subtract 1 from each individual number in a list of ten numbers going down the column, and another formula that adds 1 2 and 3 from left to right of a three digit number.

555  555

444  678

333  791

Any help would be greatly appreciated.

She was made for the straitaways
She grew up hatin' Chevrolets
She's a rocket
She was made to burn. ~Kathy Mattea

    hypersoniq's avatar - 8ball
    Pennsylvania
    United States
    Member #1340
    April 6, 2003
    2450 Posts
    Offline
    Posted: July 28, 2007, 12:04 pm - IP Logged

    would your output be

    555  555  <<< input
    444  678
    333  789
    222  890
    111  901
    000  012
    999  123
    888  234
    777  345
    666  456

    ?

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

      Rocket 455's avatar - rocket
      Bellevue, KY
      United States
      Member #53340
      July 4, 2007
      2622 Posts
      Offline
      Posted: July 28, 2007, 12:13 pm - IP Logged

      Close.. it would be

      A1    B1

      555  555

      444  678

      333  791

      222  814

      111  937

      000  050

      999  173

      888  296

      777  319

      666  432

      She was made for the straitaways
      She grew up hatin' Chevrolets
      She's a rocket
      She was made to burn. ~Kathy Mattea

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

        I have one that looks like

         

        555555 555 019 
         444 678 120 
         333 789 231 
         222 890 342 
         111 901 453 
         000 012 564 
         999 123 675 
         888 234 786 
         777 345 897 
         666 456 908 
                     

         

        If you want it, e-mail hypersoniq@comcast.net and I'll send it to you.

        Thee are long and complex nested "if" statements to handle the wraparound of 9 and 0, but I like the simplicity and faster recalculation time of using a lookup table (the greyed numbers)

        all you need to do is enter any 3 digit number in the A1 (green) cell and it figures out the rest.

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

          hypersoniq's avatar - 8ball
          Pennsylvania
          United States
          Member #1340
          April 6, 2003
          2450 Posts
          Offline
          Posted: July 28, 2007, 12:18 pm - IP Logged

          ok, i see what you are doing now...

          here is the latest

           

          555555 555 01239 
           444 678 12340 
           333 791 23451 
           222 814 34562 
           111 937 45673 
           000 050 56784 
           999 173 67895 
           888 296 78906 
           777 319 89017 
           666 432 90128 
                         

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

            Rocket 455's avatar - rocket
            Bellevue, KY
            United States
            Member #53340
            July 4, 2007
            2622 Posts
            Offline
            Posted: July 28, 2007, 12:29 pm - IP Logged

            I have one that looks like

             

            555555 555 019 
             444 678 120 
             333 789 231 
             222 890 342 
             111 901 453 
             000 012 564 
             999 123 675 
             888 234 786 
             777 345 897 
             666 456 908 
                         

             

            If you want it, e-mail hypersoniq@comcast.net and I'll send it to you.

            Thee are long and complex nested "if" statements to handle the wraparound of 9 and 0, but I like the simplicity and faster recalculation time of using a lookup table (the greyed numbers)

            all you need to do is enter any 3 digit number in the A1 (green) cell and it figures out the rest.

             I bet I can figure out how do do what I'm trying to do by looking at that code. I shot you an email.

            Thanks!

            She was made for the straitaways
            She grew up hatin' Chevrolets
            She's a rocket
            She was made to burn. ~Kathy Mattea

              hypersoniq's avatar - 8ball
              Pennsylvania
              United States
              Member #1340
              April 6, 2003
              2450 Posts
              Offline
              Posted: July 28, 2007, 12:34 pm - IP Logged

              no problem.

              soon as i get your email I'll send it out.

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

                Rocket 455's avatar - rocket
                Bellevue, KY
                United States
                Member #53340
                July 4, 2007
                2622 Posts
                Offline
                Posted: July 28, 2007, 12:39 pm - IP Logged

                ok, i see what you are doing now...

                here is the latest

                 

                555555 555 01239 
                 444 678 12340 
                 333 791 23451 
                 222 814 34562 
                 111 937 45673 
                 000 050 56784 
                 999 173 67895 
                 888 296 78906 
                 777 319 89017 
                 666 432 90128 
                               

                 That's the ticket!

                She was made for the straitaways
                She grew up hatin' Chevrolets
                She's a rocket
                She was made to burn. ~Kathy Mattea

                  hypersoniq's avatar - 8ball
                  Pennsylvania
                  United States
                  Member #1340
                  April 6, 2003
                  2450 Posts
                  Offline
                  Posted: July 28, 2007, 12:42 pm - IP Logged

                  comcast  may have fast surfing,but the e-mail is screwy sometimes...

                  still have an empty inbox,try re-sending... if not just PM your e-mail

                  gotta love technology

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

                    Rocket 455's avatar - rocket
                    Bellevue, KY
                    United States
                    Member #53340
                    July 4, 2007
                    2622 Posts
                    Offline
                    Posted: July 28, 2007, 12:56 pm - IP Logged

                    comcast  may have fast surfing,but the e-mail is screwy sometimes...

                    still have an empty inbox,try re-sending... if not just PM your e-mail

                    gotta love technology

                     Yeah I remember Comcast days... "Thank you for calling Comcast, your solutions provider, this is CSR Keptindark, how may I assist you." LOL. Nah it's fuse.net this time, the server is refusing my own return email address. I'll figure out what's going on asap and send you that email.

                    She was made for the straitaways
                    She grew up hatin' Chevrolets
                    She's a rocket
                    She was made to burn. ~Kathy Mattea

                      hypersoniq's avatar - 8ball
                      Pennsylvania
                      United States
                      Member #1340
                      April 6, 2003
                      2450 Posts
                      Offline
                      Posted: July 28, 2007, 2:51 pm - IP Logged

                      sheet is on the way...

                      just for kicks I opened it with both MS excel and OpenOffice Calc and it's interchangeable.

                      file is called Workout.xls

                      good luck with it!

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

                        Rocket 455's avatar - rocket
                        Bellevue, KY
                        United States
                        Member #53340
                        July 4, 2007
                        2622 Posts
                        Offline
                        Posted: July 28, 2007, 7:24 pm - IP Logged

                         Works great in OpenOffice Calc. Now Big Grin, how hard would it be to compare the two sets and turn any matching sets red as below. The 32 should be 032 but calc removes the 0... I can probably figure that one out eventually... The third column should start out 587 but I see what I did wrong now.

                        32032
                        032
                        01239
                        000
                        537

                        921
                        155
                        12340
                        123
                        426

                        810
                        278
                        23451
                        246
                        315

                        709
                        391
                        34562
                        369
                        204

                        698
                        414
                        45673
                        482
                        193

                        587
                        537
                        56784
                        505
                        082

                        476
                        650
                        67895
                        628
                        971

                        365
                        773
                        78906
                        741
                        860

                        254
                        896
                        89017
                        864
                        759

                        143
                        919
                        90128
                        987
                        648

                        She was made for the straitaways
                        She grew up hatin' Chevrolets
                        She's a rocket
                        She was made to burn. ~Kathy Mattea

                          hypersoniq's avatar - 8ball
                          Pennsylvania
                          United States
                          Member #1340
                          April 6, 2003
                          2450 Posts
                          Offline
                          Posted: July 29, 2007, 12:05 pm - IP Logged

                          the leading 0 is easy, I must have overlooked that...

                          in openoffice calc, highlight the cell

                          then on the menu bar select format and then cell

                          under the numbers tab

                          click "user-defined" (in excel it's under "custom")

                          and near the bottom where it says Format Code

                          enter 000

                          this will display leading zeroes, be it 032 or 001.

                          use 0000 for pick4

                          the color coding i will need to work on... only exact matches?

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

                            Rocket 455's avatar - rocket
                            Bellevue, KY
                            United States
                            Member #53340
                            July 4, 2007
                            2622 Posts
                            Offline
                            Posted: July 29, 2007, 5:28 pm - IP Logged

                              Works like a charm! Yeah the numbers are exact matches only in reverse, sometimes there won't be any matches like with some doubles.  Can't see the graphics in this one but it's looking sweet.


                            038038
                            038
                            01239
                            583
                            533KY
                            Pick 3927
                            151
                            12340
                            606
                            422
                            816
                            274
                            23451
                            729
                            311
                            705
                            397
                            34562
                            842
                            200
                            694
                            410
                            45673
                            965
                            199
                            583
                            533
                            56784
                            088
                            088
                            472
                            656
                            67895
                            101
                            977
                            361
                            779
                            78906
                            224
                            866
                            250
                            892
                            89017
                            347
                            755
                            149
                            915
                            90128
                            460
                            644

                            She was made for the straitaways
                            She grew up hatin' Chevrolets
                            She's a rocket
                            She was made to burn. ~Kathy Mattea