Results 1 to 10 of 10

sig figs

This is a discussion on sig figs within the Excel Questions forums, part of the Question Forums category; can excel round to a certain number of sig figs w/o using the scientific function (i.e 8.47, 203, 42.5) all ...

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    9

    Default

    can excel round to a certain number of sig figs w/o using the scientific function (i.e 8.47, 203, 42.5) all of those values have 3 sig figs, is there a function that will do this??? thanx for your help!!!

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default

    I don't have an answer, but have a look at this thread:

    http://www.mrexcel.com/board/viewtop...c=5521&forum=2

    The last post may be useful.

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi,

    This was posted yesterday to your other thread on this issue. Let us know if this will work for you.

    With your raw data in A1 and the number of significant figures you desire in B1, try:

    =ROUND(A1,B1-1-INT(LOG10(ABS(A1))))

    Bye,
    Jay

  4. #4
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default

    I didn't realise I referred you to yor own thread.

    It was Jay's suggestion I was referring to though, so it's in this thread now.

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    9

    Default

    the "<"text($C$24,0.000) did work for my previous question thatnx again!!

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    9

    Default

    ok, jay's answer works, except for when theres an answer like 0.0468, i believe excel only recognizes numbers other than 0. i need an answer of .047. any help is always appreciated!! thanxs all

  7. #7
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi,

    =ROUND(A1,B1-1-IF(INT(ABS(A1)),0,1)-INT(LOG10(ABS(A1))))

    This will not automatically adjust as above for a number like 0.000468, though.

    Bye,
    Jay

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    9

    Default


    jay, your first solution worked a little better for me. if i could only use a decimal format with the equation........

    its a tough problem, but if you or anyone else can help, its always appreciated!!

    marc

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040

    Default

    Hi,

    How did the first one work better? The amended version was exactly the same as the first if the integer part was not zero. This should have improved the situation somewhat.

    Are you referring to something like 55.0468 returning 55 rather than 55.0? I don't know any way to work around that unless you format the cell, although you may be able to play with the TEXT function.

    The problem with the second one returning 0.047 is that it is really only 2 significant figures, not 3.

    Jay

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    9

    Default


    ok, i got it, by using your 1st equation:

    With your raw data in A1 and the number of significant figures you desire in B1=3, try:

    =ROUND(A1,B1-1-INT(LOG10(ABS(A1))))

    and adding an additional if statement in another cell, that:

    With your raw data in A1, the number of significant figures you desire in B2=2 and your previous cell in C3, try:

    =IF(C3<.1,ROUND(A1,B2-1-INT(LOG10(ABS(A1)))),C3)

    this takes care of the problem, when numbers are less than .1, and therefore solves the problem.

    marc

    really appreciate your help all these times guys!!

    marc


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com