With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

I need to take a double variable and cut off any digits two characters to the right of the decimal point. It cannot be rounded, must be cut off. The number of digits both sides of the decimal will vary. I can't seem to find a method for that. Can someone tell me a way to do this?

Thanks, Lynn

Thanks, Lynn

Int(YourNumber *10^x)/ 10^x

where 'x' is the number of decimals you want. I've done it that way for a number of years, simply because I find it easier then counting zero's. Not as efficent of course, but I've gotten caught a couple of times by not having enough zero's on one or both sides.

FWIW,

JimD.

I really like that method. I'm going to add that to my toolkit as:

Public Function fnFix(SomeValue As Variant, Optional Decimals As Integer = 2) As Variant

fnFix = Int(SomeValue * 10 ^ Decimals) / 10 ^ Decimals

End Function

Not quite as elegant as yours...this is from A2 days.

Also have attached RoundSignificant(), which I've never really seen before and ended up writting to answer a question here on EE a few years back. You may find it useful, although I've never found an occasion to use it yet. I'm not sure I really understand it's application (Patrick may have some input on that).

JimD.

```
' Accepts: a variant value
' Purpose: converts multiplace decimal numbers
' Returns: a number rounded to d decimal places
' or a zero if the value it was called for was null
' If d is negative or null d is set to 0 and the function is like Int()
' In any case d is set to Int(d)!
' Author: Marcus O. M. Grabe, CIS 100120,1405
' Please send a message, if you like it or if you have any suggestions.
Function Round(n, d)
On Error Resume Next
If IsNull(n) Or IsNull(d) Then
Round = 0
Else
If d < 0 Then
d = 0
Else
d = Int(d)
End If
Round = CLng(n * (10 ^ d)) / (10 ^ d)
End If
End Function
Public Function RoundSignificant(varValue As Variant, intNumSignificantDigits As Integer) As String
Dim strPrefix As String
Dim dblFactor As Double
Dim dblABSofValue As Double
Dim strFormatedValue As String
Dim lngPos As Long
Dim bolDecimalPoint As Boolean
Dim strChr As String
Dim lngNumOfDigits As Long
' Check for a prefix ('>' or '<'). If there is one,
' strip it off for now.
If left(varValue, 1) = ">" Or left(varValue, 1) < "<" Then
strPrefix = left(varValue, 1)
varValue = Val(Mid(varValue, 2))
Else
strPrefix = ""
End If
' Check for null
varValue = Nz(varValue, 0)
' If value of zero, return "N/A"
If varValue = 0 Then
RoundSignificant = "N/A"
Else
' Get the factor
dblFactor = 10 ^ Int(Log(Abs(varValue)) / Log(10) - intNumSignificantDigits + 1)
' Based on the factor, get an absolute value that's rounded.
dblABSofValue = Int(Abs(varValue) / dblFactor + 0.5) * dblFactor
' Format the value as a string.
strFormatedValue = Format((IIf(varValue >= 0, 1, -1) * dblABSofValue), "#0.00000000000000000000")
' Do we have a decimal point?
If InStr(strFormatedValue, ".") > 0 Then
' If so, chop off all zeros on the right
While right(strFormatedValue, 1) = "0"
strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
Wend
End If
' Scan for the number of digits in the string
For lngPos = 1 To Len(strFormatedValue)
strChr = Mid(strFormatedValue, lngPos, 1)
If strChr > "0" And strChr <= "9" Then
lngNumOfDigits = lngNumOfDigits + 1
End If
If strChr = "." Then
bolDecimalPoint = True
End If
Next
' Is the number of digits found less then the significance required?
If lngNumOfDigits < intNumSignificantDigits Then
' If so and we have decimal point, add some zeros
If bolDecimalPoint = True Then
strFormatedValue = strFormatedValue & String(intNumSignificantDigits - lngNumOfDigits, "0")
End If
End If
' Do we have anything to the right of the decimal?
' If not, remove the decimal point
If right(strFormatedValue, 1) = "." Then
strFormatedValue = left(strFormatedValue, Len(strFormatedValue) - 1)
End If
' Add the prefix back in if we have one
If strPrefix <> "" Then
RoundSignificant = strPrefix & strFormatedValue
Else
RoundSignificant = strFormatedValue
End If
End If
End Function
```

I would disagree, yours includes the necessary error handling to prevent me from inadvertantly raising the value to the 2.3 power and handles NULLs, which is always a good idea when working with variants.

Int(SomeVariant * 10 ^ Decimals) / 10 ^ Decimals

would return a Double anyway. Thus:

Public Function fnFix(dblValue As Double, Optional intDecimals As Integer = 2) As Double

fnFix = Int(dblValue * 10 ^ intDecimals) / 10 ^ intDecimals

End Function

As for the posted RoundSignificant function, this is quite clumsy. Ignoring that it probably is made for some specific purpose because of the filtering for "<" and ">", it is still convoluted and - worst - will fail in any international environment where the decimal point is not a dot. Except for very special cases, numbers should _always_ be handled like numbers, not strings or anything else.

Below is a proven method which demands that you recall some of the math lessons in school about logarithmics.

Rounding like this is very useful for example for converting a pricelist neatly rounded in one currency to another currency maintaining the same level of rounding.

Here are four colums:

1: Amount in EUR

2: Amount in DKK

3: Amount in DKK rounded by 100

4: Amount in DKK rounded to two significant digits

15 111,45 100 110

150 1114,5 1100 1100

1500 11145 11100 11000

15000 111450 111500 110000

150000 1114500 1114500 1100000

And here in the other direction:

1: Amount in DKK

2: Amount in EUR

3: Amount in EUR rounded by 100

4: Amount in EUR rounded to two significant digits

110 14,8048452220727 0 15

1100 148,048452220727 100 150

11000 1480,48452220727 1500 1500

110000 14804,8452220727 14800 15000

Have fun!

/gustav

```
Public Function RoundSignificantCurrency( _
ByVal curValue As Currency, _
ByVal bytSignificantDigits As Byte, _
Optional ByVal booInteger As Boolean) _
As Currency
' Rounds curValue to bytSignificantDigits digits.
'
' Performs no rounding if bytSignificantDigits is zero.
' Rounds to integer if booInteger is True.
'
' Rounds correctly curValue until max/min value of currency type multiplied with
' 10 raised to the power of (the number of digits of the index of curValue) minus
' bytSignificantDigits.
' This equals roughly +/-922 * 10 ^ 12 for any value of bytSignificantDigits.
'
' Requires:
' Function Log10.
'
' 2001-10-19. Cactus Data ApS, CPH.
' 2002-04-02. Added CDec() for correcting bit errors of reals.
' 2007-04-18. Int replaced with Fix to round negative values correctly.
' Parameter booInteger made Optional.
Dim dblTmp As Double
Dim dblFactor As Double
Dim dblPower As Double
' No special error handling.
On Error Resume Next
If bytSignificantDigits = 0 Or curValue = 0 Then
' Nothing to do.
Else
dblPower = Int(Log10(Abs(curValue))) + 1 - bytSignificantDigits
If booInteger = True Then
' No decimals.
If dblPower < 0 Then
dblPower = 0
End If
End If
dblFactor = 10 ^ dblPower
dblTmp = curValue / dblFactor
dblTmp = Fix(dblTmp + Sgn(dblTmp) / 2)
' Apply CDec() to correct for possible bit error when multiplying reals.
curValue = CDec(dblTmp * dblFactor)
End If
RoundSignificantCurrency = curValue
End Function
Public Function Log10( _
ByVal dblValue As Double) _
As Double
' Returns Log 10 of input dblValue.
' No error handling as this should be handled
' outside this function.
'
' Example:
'
' If dblMyValue > 0 then
' dblLogMyValue = Log10(dblMyValue)
' Else
' ' Do something else ...
' End If
Log10 = Log(dblValue) / Log(10)
End Function
```

All Courses

From novice to tech pro — start learning today.