Welcome Guest
Log In | Register )
You last visited December 11, 2016, 6:48 am
All times shown are
Eastern Time (GMT-5:00)

Need an Excel formula

Topic closed. 7 replies. Last post 2 years ago by Murgatroyd.

Page 1 of 1
PrintE-mailLink
Rakster's avatar - praying hands.jpg
Saskatchewan
Canada
Member #19992
August 9, 2005
2867 Posts
Offline
Posted: March 2, 2015, 10:21 pm - IP Logged

I want to "minus" -111 from a number but I want to show a "positive" number that would be a 3 digit number.

Can anyone do this in Excel

for example...

439
328
217
106
-005
-116
-227
-338

as you can see 106 minus -111 should be 995 not -005

it then screws up everything after... can someone please formulate this for excel for me please.

thank you

We are all Lucky... just some of us don't realize it!

    Avatar

    United States
    Member #41846
    June 23, 2006
    460 Posts
    Online
    Posted: March 3, 2015, 7:20 pm - IP Logged

    I am not very good with if statements in excel so I will do this in two steps

    assumption  your result is in row C  as in a1=106  b1=111  c1=a1-b1

    step 1: you first result ( a negative  # in this case is in cell c1)

     

    step 2  in D1 enter  =IF(C1<0,(1000+C1),C1)  now you will have the positive number you are seeking.  hide col C or ignore it

    change cell references as needed

    copy down as needed

      Avatar
      Krypton
      United States
      Member #140102
      March 11, 2013
      892 Posts
      Offline
      Posted: March 4, 2015, 6:26 pm - IP Logged

      I am not very good with if statements in excel so I will do this in two steps

      assumption  your result is in row C  as in a1=106  b1=111  c1=a1-b1

      step 1: you first result ( a negative  # in this case is in cell c1)

       

      step 2  in D1 enter  =IF(C1<0,(1000+C1),C1)  now you will have the positive number you are seeking.  hide col C or ignore it

      change cell references as needed

      copy down as needed

      Did this answer your excel needs?  If not, let me know and I'll write the formula perhaps differently but looks like Phil did great

      Stay In The Vortex, you'll be happy you did ..... Random? Seriously? You want me to believe that?

        winsumloosesum's avatar - Lottery-060.jpg
        Pennsylvania
        United States
        Member #2218
        September 1, 2003
        5387 Posts
        Online
        Posted: March 10, 2015, 9:28 am - IP Logged
          SergeM's avatar - slow icon.png
          Economy class
          Belgium
          Member #123700
          February 27, 2012
          4035 Posts
          Offline
          Posted: March 17, 2015, 6:20 pm - IP Logged

          Remarkable long piece of coding. You have Excel tradition.

            RJOh's avatar - chipmunk
            mid-Ohio
            United States
            Member #9
            March 24, 2001
            19831 Posts
            Online
            Posted: March 18, 2015, 10:37 am - IP Logged

            I want to "minus" -111 from a number but I want to show a "positive" number that would be a 3 digit number.

            Can anyone do this in Excel

            for example...

            439
            328
            217
            106
            -005
            -116
            -227
            -338

            as you can see 106 minus -111 should be 995 not -005

            it then screws up everything after... can someone please formulate this for excel for me please.

            thank you

            If you can do it with paper and pencil, why do you need Excel?

             * you don't need to buy more tickets, just buy a winning ticket * 
               
                         Evil Looking       

              SergeM's avatar - slow icon.png
              Economy class
              Belgium
              Member #123700
              February 27, 2012
              4035 Posts
              Offline
              Posted: March 18, 2015, 12:43 pm - IP Logged

              He eventually has seen my reaction on a thread of Pick 3 on Lotterypost.

              I solve the problem with one single custom function.

              The problem can be solved with Excel functions too, you don't need to program. A Google spreadsheet probably does it too, just like other Excel like programs.

              I give a lot of credit to pen and pencil, as I never forget that couple.

                Avatar
                Arizona
                United States
                Member #165073
                March 24, 2015
                220 Posts
                Offline
                Posted: March 26, 2015, 8:46 pm - IP Logged

                Try MOD(a1-111, 1000)

                (If you want to subtract from a number in a cell other than a1, adjust accordingly.)