Significant digits

[bpeirson]

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

 

[yogia]

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

 

[bpeirson]

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.

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.

[tt]
12500.
2350.
49.
51.2
1.25
0.123
[/tt]

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.

[tt]
12500
2350
49.0
51.2
1.25
0.123
[/tt]

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

 

[yogia]

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

 

[joerd]

bpeirson,

Try this custom number format:
[tt][>100]0_._0_0_0;??0.0??[/tt]
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.