×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Significant digits

Significant digits

Significant digits

(OP)
I just read through thread 770-39236. At the end an all encompassing solution is presented except I found a hitch I'm hoping someone can solve.

When using the final function complete with the formatting instructions given the significant digits are no longer presented correctly.

The columns align the numbers based on the decimal point but if a number with three sig figs like 20.0 is in a cell it is shown as "20." which implies 2 sig figs. Alternately if a 3 sig fig number like 34500 is in a cell it is shown as "34500." which implies 5 sig figs.

I would really like to use this function and have the numbers align as though they were hand written for addition but I need the numbers to be presented correctly.

If anyone can solve this please let me know.

Brad

RE: Significant digits

Hi Brad:

If I understand you correctly, the following formula should work for you ...

=ROUNDDOWN(INT(InitialNumber),LEN(INT(InitialNumber))*-1+SignificantDigit)

Let me know if this works for you.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com

RE: Significant digits

(OP)
Thanks Yogi but your code truncates small numbers at the decimal point. The number 2.154 put into your formula for 3 significant digits is returning 2. Anything smaller than 1 returns 0.

This is the function which I got from the old thread.

CODE

Public Function sigfig(my_num, digits) As Double
Dim num_places As Integer
'  Note that VBA log function returns natural logarithm.
'  Dividing VBA log / log (10) converts to the base 10 logarithm.
'  Screen out zero values to prevent error in logarithm calculation.
If my_num = 0 Then
        sigfig = 0
    Else
    num_places = -Int((Log(Abs(my_num)) / Log(10) + 1) - digits)
    If num_places > 0 Then
        sigfig = Round(my_num, num_places)
    Else
        sigfig = Round(my_num / 10 ^ (-num_places)) * 10 ^ (-num_places)
    End If
End If
End Function

The formatting suggested was a special number format of ???.??? where the number of "?" was sufficient to display all the sig figs.

This is what the result was when the 2 were combined.


12500.   
 2350.   
   49.
   51.2  
    1.25
    0.123


This was based on 3 sig figs and a fixed decimal location but the first number is shown as 5 sig figs rather than 3 and the second as 4 rather than 3 and the third as 2 rather than 3.

My wish is to have the list appear like this.


12500    
 2350    
   49.0  
   51.2  
    1.25
    0.123


This way the rules of sig figs are preserved for people who are looking at the numbers later.

RE: Significant digits

Hi Brad:

I don't know the full scope of the numbers in your inquiry -- however my modified formula hereunder where IN=InitialNumber, and SD-SignificantDigit as you have described them ...

=IF(SD>LEN(IN),IN&"."&REPT("0",SD-LEN(IN)),ROUNDDOWN(INT(IN),LEN(INT(IN))*-1+SD)&IF(OR(SD>LEN(INT(IN)),INT(IN)=0),MID(IN,LEN(INT(IN))+1,SD-LEN(INT(IN))+1+(INT(IN)=0)),""))

works for each of the following numbers in your last post ...

12500    
 2350    
   49.0  
   51.2  
    1.25
    0.123

Let me know if it works for you now.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC
http://www.energyefficientbuild.com

RE: Significant digits

bpeirson,

Try this custom number format:
[>100]0_._0_0_0;??0.0??
You'll need to modify if you have a number of significant figures different from 3, of course.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources