Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Significant Figures Function

  1. #1
    Join Date
    6th April 2004
    Posts
    25

    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

  2. #2
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,232

  3. #3
    Join Date
    10th June 2004
    Location
    NZ
    Posts
    33

    Re: Significant Figures Function

    Hi Jon

    This is brilliant. Thank you.

    Stephen

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd January 2004
    Location
    Colorado Springs
    Posts
    3,844

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    10th June 2004
    Location
    NZ
    Posts
    33

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    Wink 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)

  7. #7
    Join Date
    6th April 2004
    Posts
    25

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    10th June 2004
    Location
    NZ
    Posts
    33

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    6th April 2004
    Posts
    25

    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:

    VB:
    =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

  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,707

    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 =

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Round To n Significant Figures
    By kathyb0527 in forum EXCEL HELP
    Replies: 3
    Last Post: February 26th, 2008, 23:30
  2. Round To Significant Figures Digits
    By Ron Antweiler in forum EXCEL HELP
    Replies: 13
    Last Post: August 25th, 2007, 03:59
  3. Round to x Significant Figures
    By jimmya3556 in forum EXCEL HELP
    Replies: 4
    Last Post: September 1st, 2006, 16:09
  4. Significant Figures
    By captbluefin in forum EXCEL HELP
    Replies: 2
    Last Post: June 23rd, 2004, 22:21
  5. Code or function to convert figures in to text
    By ahmer1 in forum EXCEL HELP
    Replies: 8
    Last Post: April 21st, 2003, 21:36

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno