Rounding

[Guest]

I am having trouble with rounding on a report. I have a list of tickets and
have them by ticket # and then I have the total summed at the end of each
ticket but rounding is incorrect.

For Example:

I want to add 100.2 + 100.2 + 100.2 = 300.6 but it is showing 300.7, what am
I doing wrong? Please help. It always seems to be rounding up.

 

[Graham Mandeno]

Hi Trina

Where are the individual amounts coming from? Are they being calculated?

I suspect that the textbox or table field formatted to show only one decimal
place, and that these three numbers are actually something like:
100.22 + 100.23 + 100.225

While each of these numbers rounds to 100.2 (1 dp), they give a sum of
300.675. If you round the result to 1 dp you get 300.7.

The solution is to round *before* you sum.

 

[Guest]

Hi Graham,

Yes these are calculated fields, the non-calculated fields are rounding
correctly. Do I need to put something in the expression to get it to round
properly?

Hi Trina

Where are the individual amounts coming from? Are they being calculated?

I suspect that the textbox or table field formatted to show only one decimal
place, and that these three numbers are actually something like:
100.22 + 100.23 + 100.225

While each of these numbers rounds to 100.2 (1 dp), they give a sum of
300.675. If you round the result to 1 dp you get 300.7.

The solution is to round *before* you sum.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having trouble with rounding on a report. I have a list of tickets
and
have them by ticket # and then I have the total summed at the end of each
ticket but rounding is incorrect.

For Example:

I want to add 100.2 + 100.2 + 100.2 = 300.6 but it is showing 300.7, what
am
I doing wrong? Please help. It always seems to be rounding up.

 

[Graham Mandeno]

Hi Trina

There's a difference between ROUNDING and FORMATTING.

As a simple example, let's say you have a field with two currency fields, A
and B, and you create a query with these two fields and a third calculated
field: C:A+B

If you populate a record with A=$2.40 and B=$3.30, you will get, not
surprisingly, C=$5.70.

Now, if you change the FORMAT of the fields in your query to show zero
decimal places, you will *see*:
A=$2 B=$3 C=$6

This is because the data still contains values to a precision that is not
showed by the format, and the calculation is performed on the data values,
not on the displayed values.

This is a problem that commonly arises with adding a percentage (say sales
tax) to several individual values and then adding them up. Let's say you
have a query with SalePrice, SalesTax, and a calculated field:
SalePriceInclTax: [SalePrice] * (1 + Nz([SalesTax], 0) )

Now, if you sell an item for $9.87 plus 12.5% tax, the calculated field
value will be $11.10375. This will, quite correctly, round to $11.10 in the
display which is formatted to 2dp.

However, if you sell two items at that price in the same order, the total
will be $22.2075, which will format as $22.21.

So the customer sees:
$11.10 + $11.10 = $22.21, and asks, "Why am I being charged an extra
cent?"

The answer is to round the values BEFORE you add them. So the calculated
field should be:
SalePriceInclTax: Round( [SalePrice] * (1 + Nz([SalesTax], 0) ), 2 )

Then, $11.10 + $11.10 will be $22.20.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Yes these are calculated fields, the non-calculated fields are rounding
correctly. Do I need to put something in the expression to get it to
round
properly?

Hi Trina

Where are the individual amounts coming from? Are they being calculated?

I suspect that the textbox or table field formatted to show only one
decimal
place, and that these three numbers are actually something like:
100.22 + 100.23 + 100.225

While each of these numbers rounds to 100.2 (1 dp), they give a sum of
300.675. If you round the result to 1 dp you get 300.7.

The solution is to round *before* you sum.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having trouble with rounding on a report. I have a list of tickets
and
have them by ticket # and then I have the total summed at the end of
each
ticket but rounding is incorrect.

For Example:

I want to add 100.2 + 100.2 + 100.2 = 300.6 but it is showing 300.7,
what
am
I doing wrong? Please help. It always seems to be rounding up.