Significant Digits
Significant Digits
(OP)
Can Excel be formatted to display a set number of significant digits rather than just decimal places?
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Contact USThanks. We have received your request and will respond promptly. Come Join Us!Are you an
Engineering professional? Join EngTips Forums!
*EngTips's functionality depends on members receiving email. By joining you are opting in to receive email. Posting Guidelines 

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.
Here's Why Members Love EngTips Forums:
Register now while it's still free!
Already a member? Close this window and log in.
RE: Significant Digits
RE: Significant Digits
RE: Significant Digits
Do you just want the same number of digits, no matter where the decimal point is? It sounds like this approach might work for your requirement. In this example, Cell B98 contains the raw number, and a 5digit figure is displayed in the cell with this formula. Obviously, you can change from 5 digits to any other number you choose, and the range can be extended up to seven nested IF functions. Hope this helps.
=IF(B98<1,ROUND(B98,5),IF(B98<10,ROUND(B98,4),IF(B98<100,ROUND(B98,3),ROUND(B98,2))))
Thus if B98=0.123456, you get 0.12346
if B98=1.23456, you get 1.2346
if B98=12.3456, you get 12.346
if B98=123.456, you get 123.46, etc.
P. S. I just remembered that, if all significant figures are to the right of the decimal point, Excel will show a zero to the left of the point, as I did above. I haven't tried to eliminate that feature. I hope it is not a problem
RE: Significant Digits
That is exactly what I was trying to do. The zero to the left of the decimal is not a problem.
Thanks
RE: Significant Digits
(i.e. 1E99 to 1E+99, and 1E99 to 1E+99).
The following formula appears to work well.
=ROUND(Number,IF(ABS(number)>1,Sigfig1TRUNC(LOG(ABS(number)),0),SigfigTRUNC(LOG(ABS(Number)),0)))
Where Number = the number you want displayed to a number of significant figures
sigfig = the number of significant figures you want displayed.
e.g. Number = 1234.5678
sigfig = 1, Result = 1000
sigfig = 2, Result = 1200
sigfig = 3, Result = 1230
sigfig = 4, Result = 1234 etc.
RE: Significant Digits
=ROUND(number,(INT(LOG(number)+1num_digits)))
Can someone please code this into a user defined VBA function?
Such as Sigfig(number, num_digits)
I've tried but cannot get it to work as a userdefined function, but it seems it should be straightforward. I have limited VBA skills.
Thanks!
RE: Significant Digits
BLT
RE: Significant Digits
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)
sign = my_num / Abs(my_num)
If num_places > 0 Then
sigfig = Round(my_num, num_places) * sign
Else
sigfig = Round(my_num / 10 ^ (num_places)) * 10 ^ (num_places) * sign
End If
End If
End Function
RE: Significant Digits
For the rest, it works fine.
By the way, VBA has a Sgn function, no need for my_num/Abs(my_num)
Cheers,
Joerd
Please see FAQ731376 for tips on how to make the best use of EngTips.
RE: Significant Digits
RE: Significant Digits
If they are recording data, where the significant figures are different. Is there anyway to have the formulas analyze the data and report values in the correct significant figures.
For example:
The Unit Under Test reads 12.2
The Standard Instrument reads 12.186
The result of the formula using this data should be in the form of X.X.
But this same sheet is going to be used for other equipment so there will be many combinations of this sort.
RE: Significant Digits
I have been playing with the significant figure function as noted above and as revised below. WHat I've found is that if the cell that contains the userdefined funtion is formatted "general", it will display the value with the correct number of digits. If you change to a "number" format, it will fix the number of decimals  not what you want!
The function below requires the # of digits as input. It sounds like you want to calculate the number of digits from what the technician enters, which will likely be very tricky. How will you handle if the value is 12.00? That's 4 significant figures, but if you enter it as a number Excel will treat it as 2 characters... If you enter '12.00 it will come in as text, and count as 5 significant digits because the point counts as well as the numerals. My point is, it will be much easier to input the correct number of digits than to compute it from the technician's input test value. Then the function can display the value rounded to the correct number of sig digits. If this approach is unacceptable, you may be forced to use text input values and then have some hidden text operations to compute the correct number of digits.
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
RE: Significant Digits
RE: Significant Digits
Use the sigfig formula above with a custom format (e.g. ???.???) with enough digits to accomodate the numbers in your list.
Wooohooo!!!!!!!