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
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
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
This is the function which I got from the old thread.
CODE
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
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
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.