# RoundUp Function not giving same answer as Excel RoundUp

G

#### Guest

Hello.

I am converting a spreadsheet inventory system to Access and trying to match
the calculations being used on the spreadsheet. The formula in Excel reads:
=ROUNDUP((B23/PI())-(2*B30),4). Substituting values the formula would be
=ROUNDUP((14.25/3.14159265358979)-(2*0.085),4) which gives me a value of
4.366.

I have looked at other posts and came up with the following function:
Public Function Roundup(RoundMe As Double)
End If
End Function

Using the above function in Access, I have the following expression:
RoundUP((14.25/3.14159265)-(2*0.085)) which gives me a value of 5.3659.

I have tried various different ways of looking at this, and I am stumped!

Denise

Try this function:

Public Function RoundXDec(ByVal varValue As Variant, _
ByVal iNum As Integer) As Variant
'*** THIS FUNCTION ROUNDS A NUMBER TO THE NUMBER
'*** OF DECIMAL PLACES SPECIFIED
'*** IN THE "iNum" VALUE.

' iNum holds the number of decimal places to be rounded to
' lNum holds the value of 10 raised to the power of iNum value
' varValue holds the value that is to be rounded
' xVal used as temporary storage of value
' xVar used as temporary storage of value

Dim lNum As Long, xVal As Double, xVar As Variant

'truncate the number to eliminate the fractional part
xVar = Fix(varValue)
'get the factor by which the fractional part will be multiplied and divided
' so that the proper rounding will be done
lNum = 10 ^ iNum

'get the rounded number result
xVal = xVar + CLng((varValue - xVar) * lNum) / lNum

RoundXDec = xVal

End Function

So, using your example in the Immediate Window:

?RoundXDec((14.25/3.14159265358979)-(2*0.085), 3)
4.366

THANK YOU!
Works like a charm!

Ken Snell (MVP) said:
Try this function:

Public Function RoundXDec(ByVal varValue As Variant, _
ByVal iNum As Integer) As Variant
'*** THIS FUNCTION ROUNDS A NUMBER TO THE NUMBER
'*** OF DECIMAL PLACES SPECIFIED
'*** IN THE "iNum" VALUE.

' iNum holds the number of decimal places to be rounded to
' lNum holds the value of 10 raised to the power of iNum value
' varValue holds the value that is to be rounded
' xVal used as temporary storage of value
' xVar used as temporary storage of value

Dim lNum As Long, xVal As Double, xVar As Variant

'truncate the number to eliminate the fractional part
xVar = Fix(varValue)
'get the factor by which the fractional part will be multiplied and divided
' so that the proper rounding will be done
lNum = 10 ^ iNum

'get the rounded number result
xVal = xVar + CLng((varValue - xVar) * lNum) / lNum

RoundXDec = xVal

End Function

So, using your example in the Immediate Window:

?RoundXDec((14.25/3.14159265358979)-(2*0.085), 3)
4.366