Announcement

Collapse
No announcement yet.

Significant Figures Function

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Significant Figures Function



    After a few hours of brain crunching, I finally figured out a fairly concise Excel function for rounding to a specific number of significant digits:

    =ROUND(value,sigfigs-(1+INT(LOG10(ABS(value)))))

    For more information about this function, you can see the write-up on my site. I also included a corresponding custom Excel VBA function.

    Rounding Significant Figures in Excel

    Enjoy,
    Jon
    The Excel Nexus :: Excel Templates

  • #2
    Thanks for sharing!
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

    Comment


    • #3
      Re: Significant Figures Function

      Hi Jon

      This is brilliant. Thank you.

      Stephen

      Comment


      • #4
        Re: Significant Figures Function

        Jon,

        Pretty slick method.

        Now next question. Can you make it so that if I enter two numbers that it will perform some indicated math and internally figure out the answer to the correct significant number of digits.


        =sigFig(strArith,num1,num2)

        Now I realize that you may have to indicate if one number was an exact number. I ask because if you use multiple calls then it would be easy to create make formula to perform calculations for say a chemistry class that always have multiple steps but sig figs are extremely important.


        Regards,
        Barry
        Regards,
        Barry

        My Favorite New Thing:
        Dynamic Named Ranges



        The alternative for
        "Press Any Key To Continue."

        and we all have one we'd like to use it on

        1. Cross Posting Etiquette
        2. Are You Here To Learn: What Have You Tried?
        3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

        Comment


        • #5
          Re: Significant Figures Function

          Hi Jon

          It seems this doesn't work with some numbers. eg show 1 to 2sf should display 1.0, but shows 1 instead. Only get 1.0 if you format the no. of dps, which then prevents the correct sf appearing.
          I didn't test this, but Steve Aprahamian did, and, as per usual, he got the finer detail that lesser mortals like me missed! Apart from that shortcoming, it works really well.
          Cheers
          Stephen

          Comment


          • #6
            Re: Significant Figures Function

            Nice work Jon.

            The display isn't right... but the value seems to be fine.

            Most will be content just having something to give correct values for computations.

            Good point though Kiwi.
            Sub All_Macros(Optional control As Variant)

            Comment


            • #7
              Re: Significant Figures

              Thank you all for your comments, especially for catching the display problem. I have added a note to my page about this. I don't see a simple solution to this problem at the moment, though.

              Jon
              The Excel Nexus :: Excel Templates

              Comment


              • #8
                Re: Significant Figures

                Steve Aprahamian suggested using =TEXT(....,"0.000"), etc to get it looking right, then using =VALUE(cell...) to handle it as a number. Sounds ok, so long as we can make the correct no. of zeros appear in the TEXT formula. Make sense?
                KiwiSteve

                Comment


                • #9
                  Re: Significant Figures Function

                  KiwiSteve,

                  Thanks for the tip. I found a similar tip to use TEXT on this page.

                  I have since figured out a solution (albeit a very complicated one). The new formula is now posted on my site, but I've included it here also:

                  Code:
                  =TEXT(TEXT(value,"."&REPT("0",sigfigs)&"E+000"), 
                  "0"&REPT(".",(sigfigs-(1+INT(LOG10(ABS(value)))))>0)& 
                  REPT("0",(sigfigs-(1+INT(LOG10(ABS(value)))))* 
                  ((sigfigs-(1+INT(LOG10(ABS(value)))))>0)))
                  I just finished creating a corresponding VBA function, which I will post on my site soon. There was a problem in the function that was driving me crazy when the value was equal to 10, 100, 1000, etc.

                  It turns out that in most cases, you don't really need to use VALUE(cell), because Excel's smart enough to figure out that the string is numeric. However, I don't know how far I'd trust that.

                  Enjoy,
                  Jon
                  The Excel Nexus :: Excel Templates

                  Comment


                  • #10
                    Re: Significant Figures Function

                    Hi Jon

                    You can also use +0 at the end of any formula that returns a number as text, or precede the formula with;
                    =-- as apposed to only =

                    Comment


                    • #11
                      Re: Significant Figures Function

                      Thanks, Dave.

                      So, if you aren't worried about displaying the trailing zeros, you could use

                      =--ROUNDSF(value,sigfigs)
                      or
                      =ROUNDSF(value,sigfigs)+0

                      Good suggestion.

                      I just finished putting the code on my site:
                      ROUNDSF

                      Enjoy,
                      Jon
                      The Excel Nexus :: Excel Templates

                      Comment


                      • #12
                        Re: Significant Figures

                        To fix the cell showing 1.0 instead of just 1:

                        Conditionally format your cells so that when =len(a1)=1 changes your formatting to Number w/ 1 decimal point (or however many sig figs you want)
                        Last edited by nickdb; June 9th, 2012, 07:54. Reason: Correction

                        Comment


                        • #13
                          Re: Significant Figures Function

                          This is a great solution for significant figures using Excel formulas, however there are a few numbers that do "break" it. Here they are:

                          99.95 - 99.999999999999999
                          9.995 - 9.999999999999999
                          0.9995 - 0.999999999999999
                          0.09995 - 0.099999999999999
                          etc., etc. all the way down until we reach Excel's limit for storing decimal places.
                          and finally, Zero. Zero results in a #NUM error due to the LOG10 function.

                          One solution is to manually add an exception for each of these cases using if statements.
                          Other than that, this is a pretty bulletproof formula and I see no issue with using the VALUE function to perform additional calculations on these sig'd numbers.

                          Cheers!

                          Comment


                          • #14


                            Re: Significant Figures Function

                            In the above post, 99.95 - 99.999999999999999 represents a range of numbers. It should be read as "every possible number Excel can store between and including 99.95 to 99.999999999999999". Or if you'd prefer it in an Excel formula format: =AND(value>=99.95,value<=99.999999999999999)

                            Dont forget the negatives:
                            =AND(value>=-99.999999999999999,value<=-99.95)

                            Cheers!

                            Comment

                            Working...
                            X