division in access to round-up if division not whole number

[ddiel]

when dividing a by b giving c; need to round-up c if it is not a whole number
Excel has roundup but Access is devoid of this function.

 

[Pendragon]

Round( a / b ) should give you what you need. That's actually 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.

 

[Michael J. Strickland]

when dividing a by b giving c; need to round-up c if it is not a whole
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

 

[John Spencer]

If the number is always positive it is fairly simple to force it to the next
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

 

[David W. Fenton]

Round( a / b ) should give you what you need. That's actually
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)?

 

[Jack Leach]

?round(3.3,0)
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)

 

[Jack Leach]

What am I talking about?

?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)

 

[Michael J. Strickland]

What am I talking about?

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

 

[David W. Fenton]

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.

 

[Michael J. Strickland]

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

 

[David W. Fenton]

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