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

Help with Millions verses Thousands formula

Topic closed. 7 replies. Last post 7 years ago by KnuckleHead.

Page 1 of 1
PrintE-mailLink
KnuckleHead's avatar - box

United States
Member #73037
April 3, 2009
147 Posts
Offline
Posted: June 6, 2009, 1:36 pm - IP Logged

Morning all,

Does anyone know if a formula can be written to look at a cell that contains say "$25,600,000.00" or $480,000.00", then the displayed answer would be either "25.6 M" or "480 K".

In other words, the cell has either thousands or millions in it, and I would like to take whatever is in that cell and shorten it to either a "M" for millions or a "K" for thousands. Can a formula look at a cell and tell the difference in what to display as the answer since each draw amount would change?

Thanks ahead of time for any interest and help.

          The only DUMB question is the one question you DID NOT ask...

    Raven62's avatar - binary
    New Jersey
    United States
    Member #17843
    June 28, 2005
    49642 Posts
    Offline
    Posted: June 6, 2009, 10:02 pm - IP Logged

    Data in Cell A3

    In Cell B3:
    @IF(A3<1000000,@STRING(A3/1000,2)&"K",@STRING(A3/1000000,2)&"M")

    A mind once stretched by a new idea never returns to its original dimensions!

      KnuckleHead's avatar - box

      United States
      Member #73037
      April 3, 2009
      147 Posts
      Offline
      Posted: June 7, 2009, 12:50 pm - IP Logged

      Thank you  Raven62

       

      I've been trying to figure that one out. Needless to say, I'm not computer literate.

      I copied your formula into the cell with the correct "target cell" in it and got the answer with 2 decimals. Looking at it, I modified it from:

      @IF(A3<1000000,@STRING(A3/1000,2)&"K",@STRING(A3/1000000,2)&"M")

      TO:

      @IF(A3<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,0)&" M")

      Works great...Thank you!

       

      Now with that figured out, is there a way to force the answer to become a number instead of a formula?

      I have a row that contains "recent" draw info. That's what the above formula is doing, converting the winning amount from the "recent" row to the "history" row. If I insert a row, the formula will always refer to the original cell even if the contents change. That could be a problem. So, is there a way to "freeze" the answer, so that it doesn't recalculate based on new info being entered?

      Thanks again.

                The only DUMB question is the one question you DID NOT ask...

        KnuckleHead's avatar - box

        United States
        Member #73037
        April 3, 2009
        147 Posts
        Offline
        Posted: June 8, 2009, 9:55 am - IP Logged

        Morning  Raven62,

        I made a mistake with the formula,

        @IF(A3<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,0)&" M")

        It should read;

        @IF(Target Cell<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,0)&" M")

        Where "Target Cell" is the cell you want to check...

        Sorry about that.

         

        The above formula works if you start off with "$15,000,000.00" The answer is "15 M". But if you start off with "$15,200,000.00", the answer is still "15 M". The correct answer should be "15.2 M".

        I've tried including "Target Cell/100000" and "Target Cell/1000". Neither of those work either. I even tried "dividing", that also didn't work.

        The side of the formula that calculates the "K" section works properly.

        Also, I did figure out how to copy the "cell formula" and "paste the answer into another cell as a "value" instead of a formula.

        Please, what am I doing wrong? And, Thank you.

                  The only DUMB question is the one question you DID NOT ask...

          Avatar

          United States
          Member #41846
          June 23, 2006
          458 Posts
          Offline
          Posted: June 8, 2009, 12:35 pm - IP Logged

          sounds as if you have formated the cell to have no decimals.  with the target cell highlited check format cells( exact method varies with what version of exce you are usingl).  set to the number of decimal places you want.

          p8

            KnuckleHead's avatar - box

            United States
            Member #73037
            April 3, 2009
            147 Posts
            Offline
            Posted: June 9, 2009, 7:23 am - IP Logged

            Morning  phileight,

            My spreadsheet program is Lotus123, the cell with the formula is formated to "Gereral", with no decimal places.

            As I manually enter the contents ("15 M" or "15.2 M"), no problem. The formula above calculates correctly if the "target cell" has "$15,000,000.00" or "$480,000.00" in it. The "target cell" is formated as "US Dollars".

            Basicly, I'm inserting a new row into the "history" section and taking the contents of the "current winning" amount section and placing it in the "winning history" section.

            I was attempting to "shorten" the current winning amount to make it easier to read (without all the zeros) in the "history" section. As you can see above, with Raven62's assistance, I can now set the shortened amount into it just by copying the data instead of having to manually enter it. I also figured out how to set the "answer" from a formula to a "number" so that it doesn't keep "tracking the "new" amount.

            The problem is that the formula as written does not take into consideration the "hundred thousand" section in the "millions" side of the formula for the amount "$15,200,000.00". It only calculates the "millions" section. Plus I would like it to place the "." in the proper ("15.2 M") place.

            Not being computer literate, I haven't been able to figure out how to do that. I have figured out how to place the " " (space) into the formula so that the answer is properly set for the "M" or "K".

            I know, for people that write formulas all the time, it's a "no biggie", but for someone like me, it's driving me nutts.

            Thanks for the interest though, any suggestions?

                      The only DUMB question is the one question you DID NOT ask...

              Raven62's avatar - binary
              New Jersey
              United States
              Member #17843
              June 28, 2005
              49642 Posts
              Offline
              Posted: June 9, 2009, 10:45 am - IP Logged

              Morning  Raven62,

              I made a mistake with the formula,

              @IF(A3<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,0)&" M")

              It should read;

              @IF(Target Cell<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,0)&" M")

              Where "Target Cell" is the cell you want to check...

              Sorry about that.

               

              The above formula works if you start off with "$15,000,000.00" The answer is "15 M". But if you start off with "$15,200,000.00", the answer is still "15 M". The correct answer should be "15.2 M".

              I've tried including "Target Cell/100000" and "Target Cell/1000". Neither of those work either. I even tried "dividing", that also didn't work.

              The side of the formula that calculates the "K" section works properly.

              Also, I did figure out how to copy the "cell formula" and "paste the answer into another cell as a "value" instead of a formula.

              Please, what am I doing wrong? And, Thank you.

              The above formula works if you start off with "$15,000,000.00" The answer is "15 M". But if you start off with "$15,200,000.00", the answer is still "15 M". The correct answer should be "15.2 M".

              @IF(Target Cell<1000000,@STRING(Target Cell/1000,0)&" K",@STRING(Target Cell/1000000,1)&" M")

              Instead of mixing thousands and millions you may want to consider using only one:

              @STRING(Target Cell/1000000,1)&" M"

              15,200,000 = 15.2 M

              200,000 = .2 M

              Just a thought!

              A mind once stretched by a new idea never returns to its original dimensions!

                KnuckleHead's avatar - box

                United States
                Member #73037
                April 3, 2009
                147 Posts
                Offline
                Posted: June 9, 2009, 2:36 pm - IP Logged

                Afternoon Raven62

                By changing the "0" to "1". That did the trick.

                Thank you.

                          The only DUMB question is the one question you DID NOT ask...