D

#### ddiel

Excel has roundup but Access is devoid of this function.

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

D

Excel has roundup but Access is devoid of this function.

P

0) where the number to the right of the comma is the number of decimals to

round. As the helpfile in Access notes, if this is omitted, integers are

returned.

M

ddiel said:

number

Excel has roundup but Access is devoid of this function.

You can use the following function:

' Returns the smallest integer equal to or greater than sngValue

Private Function Ceiling(sngValue As Single) As Long

If sngValue = Int(sngValue) Then

Ceiling = CLng(sngValue)

ElseIf sngValue > 0 Then

Ceiling = Int(sngValue) + 1

Else

Ceiling = Int(sngValue) + 1

End If

End Function

J

integer. You can use the following expression:

-Int(-a/b)

John Spencer

Access MVP 2002-2005, 2007-2009

The Hilltop Institute

University of Maryland Baltimore County

D

Round( a / b, 0) where the number to the right of the comma is the

number of decimals to round. As the helpfile in Access notes, if

this is omitted, integers are returned.

Doesn't the VBA Round() function do so-called "bankers rounding,"

which rounds up for even last digit and down for odd last digig (or

maybe it's the other way around)?

J

3

?round(4.3,0)

4

yup... I can't imagine why anyone would ever decide to round this way, let

alone why the Access team chose this for the default rounding. It makes

absolutely no mathematical sense to round a digit based on whether it's even

or odd.

I did write a function to return a REAL rounded number (round + on numbers 5

and up), but it seems the only copy is at work, so I'll have to pull it

tomorrow.

--

Jack Leach

www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."

-Thomas Edison (1847-1931)

J

?round(3.3,0)

3

?round(3.6,0)

4

?round(4.3,0)

4

?round(4.6,0)

5

Now.. I know I've been through this before, and this didn't compute like

this (because I had to write a custom function to get it to work correctly),

but now... the above rounds exactly as should... I even remember reading

posts on how vba rounded up or down based on the odd/even value of the

number...

<scratches head>

--

Jack Leach

www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."

-Thomas Edison (1847-1931)

M

Jack Leach said:

?round(3.3,0)

3

?round(3.6,0)

4

?round(4.3,0)

4

?round(4.6,0)

5

Now.. I know I've been through this before, and this didn't compute

like

this (because I had to write a custom function to get it to work

correctly),

but now... the above rounds exactly as should... I even remember

reading

posts on how vba rounded up or down based on the odd/even value of the

number...

<scratches head>

--

Jack Leach

www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."

-Thomas Edison (1847-1931)

VBA does correct scientific rounding. It round to the nearest integer

unless the last digit is 5 (midway between), then it rounds to the

nearest even integer.

?Round(2.5,0)

2

?Round(3.5,0)

4

?Round(2.4,0)

2

?Round(2.6,0)

3

2.5 is rounded to the nearest even integer (2).

3.5 is rounded to the nearest even integer (4)

2.4 is correctly rounded down to 2, even though 4 is even.

2.6 is correctly rounded up even though 6 is even.

This form of rounding is the most accurate since the integer portion is

just as likely to be even as odd and therefore in the midway cases it is

just as likely to round up as down.

D

This form of rounding is the most accurate since the integer

portion is just as likely to be even as odd and therefore in the

midway cases it is just as likely to round up as down.

"Most accurate" only when you're aggregating the results. Human

beings expect .5 to round up, regardless. And for negative numbers

it should round Round(Abs(-N),0), instead of applying banker's

rounding the same way as for positive numbers. That's not rounding

"up" on the number line (i.e., in the positive direction), but it's

what seems to me to be intuitive.

Because of this, I still use the round function I used before VBA

added it in A2000, renamed now in my code to iRound(). If anyone's

interested in the code, I'll post it.

M

David W. Fenton said:"Most accurate" only when you're aggregating the results.

What else would you be doing with them? Your method of rounding up in

every case will definitely be less accurate in the aggregate or in in

any other situation.

Human beings expect .5 to round up, regardless.

Wrong, human beings expect rounding to produce the most accurate

results, which

is why we were all taught this method of rounding in 3rd grade

arithmetic.

And for negative numbers

For negative numbers the correct scientific rounding also produces the

most accurate results.

D

What else would you be doing with them? Your method of rounding up

in every case will definitely be less accurate in the aggregate or

in in any other situation.

If you're creating invoices, for instance, it is best to store the

tax amount, rather than the rate, since you need do the rounding

only once. Human beings expect the numbers to be rounded up for each

individual invoice.

Then when you aggregate, the results will correspond to what you'd

expect from aggregating the already-rounded numbers.

Wrong, human beings expect rounding to produce the most accurate

results, which

is why we were all taught this method of rounding in 3rd grade

arithmetic.

Banker's rounding? Nobody taught it to me! I didn't encounter it

until the 90s.

For negative numbers the correct scientific rounding also produces

the most accurate results.

I do not know what you mean by "correct scientific rounding". If you

mean "banker's rounding" then say what you mean (which completely

avoids the absolute value issue by rounding each way half the time).

In the financial apps I've dealt with (where banker's rounding was

not used), it is expected that a credit memo for $100 with a tax

amount of 8.375% will have a total of ($108.38) and a credit memo

for $100 with a tax rate of 8.365% will have a total of ($108.37).

With rounding up in all cases, you could end up with a credit memo

that is the exact amount of an invoice not adding up to the same

amount (because it runs up along the number line, instead of in the

direction of the absolute value of the quantity).