RoundUp Function not giving same answer as Excel RoundUp

[Denise]

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)
Dim RoundAnswer As Double
RoundAnswer = Round(RoundMe, 4)
If RoundAnswer < RoundMe Then
RoundAnswer = RoundAnswer + 1
End If
Roundup = RoundAnswer
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!
Can someone please help?

Thanks in advance,
Denise

 

[Ken Snell \(MVP\)]

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

 

[Denise]

THANK YOU!
Works like a charm!

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