Chi Is Current

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

'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.

# Result

-50.855 -50.85

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

2.

Int([NumberField]*100+(0.5

# 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

Does this function help:

http://www.mvps.org/access/modules/mdl0054.htm

I used it but changed Ronud into fncRound

Nic;o)

http://www.mvps.org/access/modules/mdl0054.htm

I used it but changed Ronud into fncRound

Nic;o)

ASKER CERTIFIED SOLUTION

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**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

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

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

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

ASKER

Hmmmm:

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

Works.

if([NumberField] = 0,0,Int(Abs([NumberField])

Works.

That would appear to be the best solution.

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

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

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

DECIMAL(ROUND(873.726,2),6

DECIMAL(ROUND(873.726,1),6

DECIMAL(ROUND(873.726,0),6

DECIMAL(ROUND(873.726,-1),

DECIMAL(ROUND(873.726,-2),

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