Rounding Issue

Question

I am using the formula below in an Access module. Is it possible to round this down?

Round(Me.txt_GrossWage * DLookup("WA_PaidLeave_Rate", "qry_TaxRate"), 4) * DLookup("PaidLeave_Muliplyer", "qry_TaxRate")

Answer 1

Got it. You are using a "Round Down" rule? You also want to round to two decimal points, rather than an integer.

You can achieve that by multiplying the result of your calculation by 100. That shifts the decimal two places to the right.

Next use Int() to remove any remaining decimal places from that result. Your example contains 4 decimal places, so Int() would remove the last two.

Next divide by 100 to return to the original 2 decimal places required.

Try this:

Int(Round(Me.txt_GrossWage * DLookup("WA_PaidLeave_Rate", "qry_TaxRate"), 4) * DLookup("PaidLeave_Muliplyer", "qry_TaxRate") * 100)/100

If it returns consistently correct results, you're good to go. If if not, we'll need to explore further.

Answer 2

in excel the formula looks like:

ROUNDDOWN((ROUND(215.1 * 0.0092,2)) * 0.7152,2)

Answer 3

Are you applying Bankers' Rounding, Traditional Rounding or some other rounding rule?

My understanding is that traditional rounding is done to the nearest integer. In this case, the nearest integer to 1.4157 is 1.42, not 1.41 as you require because .4157 is closer to .42 than it is to .41

Bankers' Rounding, however, has special rules for rounding values that end in exactly 5.

"... Banker’s rounding is an algorithm for rounding quantities to integers. But this algorithm is applied to the numbers that are equidistant from the two nearest integers are rounded to the nearest even integer. "

That means, using Banker's Rounding rules, 1.415 rounds to 1.42 rather than 1.42

Under Bankers' Rounding, the value 1.425 would also result in 1.42

Your value of 1.4157 doesn't round to 1.41 under either of those common rounding rules.

If your rounding rule is that all decimals are always rounded down, not to the nearest integer or nearest even integer, you can do it that way.

Gustav Brock has an extensive set of materials on rounding in VBA at hisGItHub. It includes rounding up and down as well as the more commonly used methods.

Answer 4

The formula retouns a value of $1.4157. I need it to round down to $1.41

Answer 5

“Round this down” is vague. Can you provide a more descriptive specification?