enirwin,
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 5-digit 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.
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
I have been trying to do this for some time - no idea why this function isn't built in to Excel. I wanted to do this for numbers over the whole range
(i.e. 1E-99 to 1E+99, and -1E-99 to -1E+99).
As a followup, please note that the approach I just posted will only work for positive values, hence the simpler formula. TrevorP's approach will work on positive or negative values.
After resolving some idosyncracies of VBA functions that are not the same as Excel functions (e.g. Round and Log), this is the user-defined significant figure function I've come up with, for anyone still following this 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)
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
I would remove all the "sign" stuff, because your function will convert negative values to positive (e.g. sigfig(-0.2345,2) = 0.23
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 FAQ731-376 for tips on how to make the best use of Eng-Tips.
I have a question that is along these same lines. I have a lab that I monitor that calibrates a wide range of equipment. So therefore We have a wide range of significant figures to account for. What I am running into is that as per company practice, any spreadsheets that are used by the technicians must be locked, except for the data entry cells. So now to the question...
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.
Good question,
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 user-defined 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
This is a good solution to significant figures. Thanks for posting it. Now if I could just get excel to align on the decimal and strip off trailing zeros that are not significant. Any suggestions???
