Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
Significant Digits 3
- Thread starter enirwin
- Start date
electricpete
Electrical
Format - Cells - Number - Scientific allows you to precisely control the significant digits. But not sure if you like that formatn.
-
1
- #4
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.
=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
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.
=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
- Thread starter
- #5
-
1
- #6
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).
The following formula appears to work well.
=ROUND(Number,IF(ABS(number)>1,Sigfig-1-TRUNC(LOG(ABS(number)),0),Sigfig-TRUNC(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.
(i.e. 1E-99 to 1E+99, and -1E-99 to -1E+99).
The following formula appears to work well.
=ROUND(Number,IF(ABS(number)>1,Sigfig-1-TRUNC(LOG(ABS(number)),0),Sigfig-TRUNC(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.
-
1
- #7
bltseattle
Civil/Environmental
I've come up with another Excel function similar to TrevorP's but simpler.
=ROUND(number,-(INT(LOG(number)+1-num_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 user-defined function, but it seems it should be straightforward. I have limited VBA skills.
Thanks!
=ROUND(number,-(INT(LOG(number)+1-num_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 user-defined function, but it seems it should be straightforward. I have limited VBA skills.
Thanks!
bltseattle
Civil/Environmental
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.
BLT
BLT
bltseattle
Civil/Environmental
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
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.
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.
valhalla076
Electrical
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.
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.
bltseattle
Civil/Environmental
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
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
- Status
