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

help from the great programmers please

Topic closed. 17 replies. Last post 2 years ago by SergeM.

Page 1 of 2
PrintE-mailLink
notmyday's avatar - 8ball
florida
United States
Member #136668
December 16, 2012
346 Posts
Offline
Posted: January 11, 2015, 9:36 am - IP Logged

can someone please give me the formula to take the zero -1 and get the nine instead of a negative one. example 003-111=992 i would like to do my own spreadsheet so i cand make my own modification where needed.

    Avatar

    United States
    Member #41846
    June 23, 2006
    460 Posts
    Offline
    Posted: January 11, 2015, 9:59 am - IP Logged

    for this example assume the target cell  is A1.  =mod(a1,10)   divide number by 10 show only remainder

     

    for this example  you have data in a1and  b1  your result is in c1.  in c1   =mod(a1-b1,10)   divide result by 10 show only remainder

     

    for your specific example. 3 digit numbers    =mod(a1-b1,1000)   divide result by 1000 and show only remainder

      Unkle Richie's avatar - Lottery-001.jpg
      roanoke, virginia
      United States
      Member #145502
      August 7, 2013
      304 Posts
      Offline
      Posted: January 11, 2015, 10:02 am - IP Logged

      for this example assume the target cell  is A1.  =mod(a1,10)   divide number by 10 show only remainder

       

      for this example  you have data in a1and  b1  your result is in c1.  in c1   =mod(a1-b1,10)   divide result by 10 show only remainder

       

      for your specific example. 3 digit numbers    =mod(a1-b1,1000)   divide result by 1000 and show only remainder

      Yep, What he said

        notmyday's avatar - 8ball
        florida
        United States
        Member #136668
        December 16, 2012
        346 Posts
        Offline
        Posted: January 11, 2015, 10:05 am - IP Logged

        thanks for the help much appreciated!!!!

          notmyday's avatar - 8ball
          florida
          United States
          Member #136668
          December 16, 2012
          346 Posts
          Offline
          Posted: January 11, 2015, 10:10 am - IP Logged

          i am using openoffice it is giving me error511?

            Avatar

            United States
            Member #41846
            June 23, 2006
            460 Posts
            Offline
            Posted: January 11, 2015, 10:22 am - IP Logged

            I don't use open office but from what I understand many things are very similar.  check the help file for the proper usage for the " mod " command in open office

              Avatar

              United States
              Member #41846
              June 23, 2006
              460 Posts
              Offline
              Posted: January 11, 2015, 10:26 am - IP Logged

              Syntax:

              MOD(number; divisor)

              For integer arguments this function returnsnumber modulodivisor, that is the remainder whennumber is divided bydivisor.This function is implemented asnumber - divisor * INT( number/divisor) , and this formula gives the result if the arguments are not integer.

              Example:

              MOD(22; 3)

              returns1, the remainder when22 is divided by3.

              MOD(11.25; 2.5)

              returns 1.25.

               

              they use a semi colon instead of comma

                notmyday's avatar - 8ball
                florida
                United States
                Member #136668
                December 16, 2012
                346 Posts
                Offline
                Posted: January 11, 2015, 10:44 am - IP Logged

                Syntax:

                MOD(number; divisor)

                For integer arguments this function returnsnumber modulodivisor, that is the remainder whennumber is divided bydivisor.This function is implemented asnumber - divisor * INT( number/divisor) , and this formula gives the result if the arguments are not integer.

                Example:

                MOD(22; 3)

                returns1, the remainder when22 is divided by3.

                MOD(11.25; 2.5)

                returns 1.25.

                 

                they use a semi colon instead of comma

                thanks for your help i will give that a try . THANKS AGAIN

                  notmyday's avatar - 8ball
                  florida
                  United States
                  Member #136668
                  December 16, 2012
                  346 Posts
                  Offline
                  Posted: January 11, 2015, 10:55 am - IP Logged

                  there must be something missing like the if or something now it is simply adding a neg111 to my number. sorry tobe a pain i had this last year but i didnt have my computer backedup then it had a hard drive failure so i lost it all. back then it took me a month to get it with research i was hoping to eliminate all the research.

                    Avatar

                    United States
                    Member #41846
                    June 23, 2006
                    460 Posts
                    Offline
                    Posted: January 11, 2015, 11:18 am - IP Logged

                    maybe I have misunderstood what you are trying to do    if cell  a1 is 003  and cell b1 is 111   and in cell c1 you have the formula   =mod(a1-b1;1000) then it should show 892.  if this is not what you want,  try stating the problem again with the expected answer.

                      notmyday's avatar - 8ball
                      florida
                      United States
                      Member #136668
                      December 16, 2012
                      346 Posts
                      Offline
                      Posted: January 11, 2015, 11:19 am - IP Logged

                      using003-111=992

                        notmyday's avatar - 8ball
                        florida
                        United States
                        Member #136668
                        December 16, 2012
                        346 Posts
                        Offline
                        Posted: January 11, 2015, 11:22 am - IP Logged

                        using003-111=992

                        wanting todo a -111 rundown from previous number but 0 needs to bring a 9

                          Avatar

                          United States
                          Member #41846
                          June 23, 2006
                          460 Posts
                          Offline
                          Posted: January 11, 2015, 11:27 am - IP Logged

                          to do that you will need to break the digits apart and deal with each separately.     cell a1 0  b1 0  c1 3    cell a2 1  b2 1  c2 1

                          cell e1    =mod(a1-a2;10)     cell f1   =mod(b1-b2;10)   cell g1   =mod(c1-c2;10)    cell I1  =e1 & f1 & g1

                           

                          several steps and obviously adjust cell references as needed. the last step would work in Excel  open office ???

                            notmyday's avatar - 8ball
                            florida
                            United States
                            Member #136668
                            December 16, 2012
                            346 Posts
                            Offline
                            Posted: January 11, 2015, 11:32 am - IP Logged

                            great that worked ill put more in to check thank you so much now i can get busy with the rest.

                              SergeM's avatar - slow icon.png
                              Economy class
                              Belgium
                              Member #123700
                              February 27, 2012
                              4035 Posts
                              Offline
                              Posted: January 11, 2015, 11:34 am - IP Logged

                              can someone please give me the formula to take the zero -1 and get the nine instead of a negative one. example 003-111=992 i would like to do my own spreadsheet so i cand make my own modification where needed.

                              You were doing:

                              10 - 1
                              10 - 1
                              3 - 1
                              =
                              9
                              9
                              2

                              What ever you do, you get some row where you want the unique numbers from.

                              Up to you to work it out according to your fancy ideas and your fancy rules.

                              Instead of 003 you can split to three columns to simplify the task. Excel is made for Cells, it is Excellent-Cell,
                              or excel with cells?