asked on

Rounding scheme for positive and negative numbers

Hello ~

'Seems a lot has been written about rounding yet...

I am looking for a formula to round both positive and negative decimals to two places, rounding UP (away from zero) at 5 and DOWN (away from zero) at -5.

Examples in the following table:

# Result
50.845 50.85
-50.845 -50.85
-50.850 -50.85
50.850 50.85

Attempts include:

1.
INT(([NumberField]*100)+0.5)/100

# Result
-50.855 -50.85
Result should be -50.86. All other #s are OK.

2.
Int([NumberField]*100+(0.5*IIF([NumberField]<0,-1,1)))/100

# Result
-50.850 -50.86
Result should be the same as opriginal number. All other #s are OK.

As there are 8 different, rounded NumberFields aggregated into a larger formula in a query, I'm looking for a formula I can use in a query.

Several here hope you can illuminate!

Best Regards, Jacob

Rey Obrero (Capricorn1)

you can use this function

Public Function RoundUP(dblInput As Double, intDecimals As Integer) As Double

'Implements a variant of the Round() function, that rounds-to-larger
'rather than rounds-to-even, like in Excel:

Dim strFormatString As String 'Format string
Dim strResult As String

'If input is zero, just return zero. Else format as appropriate:
If dblInput <> 0 Then
strFormatString = "#." & String(intDecimals, "#")
If Right(strFormatString, 1) = "." Then
strResult = Format(dblInput, "#")
Else
strResult = Format(dblInput, strFormatString)
End If

Else
strResult = "0"
End If

'If the result is zero, Format() will return "." - change this to "0":
If strResult = "." Then
strResult = "0"
End If

RoundUP = CDbl(strResult)

End Function

nico5038

Does this function help:
http://www.mvps.org/access/modules/mdl0054.htm

I used it but changed Ronud into fncRound

Nic;o)

ASKER CERTIFIED SOLUTION

jrb1

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Chi Is Current

ASKER

jbr1 ~

That's it! Your formula reflects elegant simplicity. I hope this formula can be made widely available to others. I know there are many folks who run into this.

Thank you!

Jacob

Chi Is Current

ASKER

jbr1~

I'm finding when NumberField = 0, an #Error results. Can you recommend a modification that would return 0, or handle instances when NumberField = 0?

Best Regards, Jacob

Chi Is Current

ASKER

Hmmmm:

if([NumberField] = 0,0,Int(Abs([NumberField])*100+.5)/100*Abs([NumberField])/[NumberField])

Works.

jrb1

That would appear to be the best solution.

Andy_Bethell

Or possibly Int(Abs([NumberField])*100+.5)/100*Sgn([NumberField]) ?

peteosborne

I believe I may be missing something obvious. Why not use the built in Round() function in Access/SQL? The function is also available in SQL. I used to use an expression like DECIMAL(ROUND(873.726,2),6,3) to round on AS/400

i.e. from https://aurora.vcu.edu/db2help/db2s0/frame3.htm#sqlintr

DECIMAL(ROUND(873.726,2),6,3) = 873.730
DECIMAL(ROUND(873.726,1),6,3) = 873.700
DECIMAL(ROUND(873.726,0),6,3) = 874.000
DECIMAL(ROUND(873.726,-1),6,3) = 870.000
DECIMAL(ROUND(873.726,-2),6,3) = 900.000