Rounding problem regarding significant figures.
Posted by Nathan Alterton on September 27, 2000 12:54 PM
Here is my rounding problem:
If the first digit discarded is less than 5, do not change the last digiat retained. For example, 3.46325, if rounded to 4 significant figures, would be 3.463, if rounded to three digits, 3.46.
If the first digit discarded is greater than 5, or if it is a 5 followed by at least one digit other than 0, increase the last digit retained by one unit. For example, 8.37652, if rounded to 4 digits, would be 8.377, if rounded to three digits, 8.38.
If the first digit discarded is exactly 5, followed only by zeros, round the last digit upward if it is odd, but make no adjustment if it is an even number. For example, 4.365, when rounded to three digits, becomes 4.36. However, the number 4.355 would also round to 4.36, if rounded to three digits.
Please help! This is a common engineering rounding practice, so someone must know how to make Excel do this type of rounding!
Thanks
Nathan Alterton
Nathan_Alterton@dot.ca.gov
Posted by Tim Francis-Wright on September 27, 2000 1:26 PM
I think that the following function will do:
Call it like cleverround(1.23456,4) to get 1.235
Function CleverRound(Item As Single, Sig As Integer)
Dim DecPart, FracPart As Single
Dim DecDigits As Integer
If Item = 0 Then
CleverRound = ""
Else
DecDigits = Sig - Application.WorksheetFunction.Max _
(0, Application.WorksheetFunction.RoundUp(Log(Abs(Item)) / Log(10), 0))
DecPart = Application.WorksheetFunction.Round(Item, DecDigits)
FracPart = Item - DecPart
If Abs(FracPart * 2) = 10 ^ (-DecDigits) Then
CleverRound = Application.WorksheetFunction.Round(Item / 2, DecDigits) * 2
Else
CleverRound = DecPart
End If
End If
End Function
HTH!
Posted by Tim Francis-Wright on September 27, 2000 1:50 PM
I realized that the rounding of 0.005 was failing
because of Excel's astounding precision for
many math operations. So... I defined the
relevant numbers as double-precision, and
made an arbitrary assumption that 10^-9 was
close enough to 0 for (pardon the irony) government work.
Function CleverRound(Item As Double, Sig As Integer) As Double
Dim DecPart, FracPart As Double
Dim DecDigits As Integer
If Item = 0 Then
CleverRound = 0
Else
DecDigits = Sig - Application.WorksheetFunction.Max _
(0, Application.WorksheetFunction.RoundUp(Log(Abs(Item)) / Log(10), 0))
DecPart = Application.WorksheetFunction.Round(Item, DecDigits)
FracPart = Item - DecPart
If 1 - Abs((FracPart * 2) / (10 ^ (-DecDigits))) < 10 ^ (-9) Then
CleverRound = Application.WorksheetFunction.Round(Item / 2, DecDigits) * 2
Else
CleverRound = DecPart
End If
End If
End Function
Posted by Nathan Alterton on September 27, 2000 2:17 PM
I got the info that was posted, however, I am just an average Excel user, and I don't know what to do with the information posted. If anyone can explain what to do with the posted info, please do.
Nathan Alterton
