best practices.rounding

I understand why Acess rounds the way it does (at least somewhat), and "bankers
rounding", so I'm not asking the basic rounding question. (just one level up
from basic ;-)

I've done rounding two ways, either Currency for both actual currency values and
and Double Number values. I've also used MS's SymArith function. (included at
end of this post, just in case anyone wondered what the function was.)

I'm wondering what the 'best practice' way is. Do you simply use Currency for
Currency, and not round to 2 decimal places. If so, then someone adding figures
up and comparing could come up with something different, because doesn't Access
store 4 digit for currency, which could have a slightly different totaled amount
than someone adding with excel, for example.

I know some people used to say to just use currency for numbers that are not
actual currency, and just format the Form or Report to not show the $ sign. That
works, but wouldn't the SymArith function be better?

Like I said, I'm just wondering what the best practice is. The few DB's I've
done work just fine, just wondering what the pros do.

Thanks, Josh

Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
' Alternately:
' SymArith = Abs(AsymArith(X, Factor)) * Sgn(X)
End Function

Josh

Define "best" ... "best" for obtaining what kind of result?

?"Best" as in "most accurate" (according to which rounding rule(s))?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Josh" wrote in message
news
I understand why Acess rounds the way it does (at least somewhat), and
"bankers
rounding", so I'm not asking the basic rounding question. (just one level
up
from basic ;-)

I've done rounding two ways, either Currency for both actual currency
values and
and Double Number values. I've also used MS's SymArith function.
(included at
end of this post, just in case anyone wondered what the function was.)

I'm wondering what the 'best practice' way is. Do you simply use Currency
for
Currency, and not round to 2 decimal places. If so, then someone adding
figures
up and comparing could come up with something different, because doesn't
Access
store 4 digit for currency, which could have a slightly different totaled
amount
than someone adding with excel, for example.

I know some people used to say to just use currency for numbers that are
not
actual currency, and just format the Form or Report to not show the $
sign. That
works, but wouldn't the SymArith function be better?

Like I said, I'm just wondering what the best practice is. The few DB's
I've
done work just fine, just wondering what the pros do.

Thanks, Josh

Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
' Alternately:
' SymArith = Abs(AsymArith(X, Factor)) * Sgn(X)
End Function

I suppose I should have used "Method most commonly used by the Pros" instead of
"best practices" because you're correct, "best" is subjective.

I guess I'm wondering about:
When using a Field whose Data Type is Number (double), whether to make it
Currency instead of Number, or to leave it as Data Type Number (double) and use
SymArith (or similar function) to further round it (Excel - type round).

I want to make sure (as far as possible) that the data that is actually stored
in the table (not formatted in a form or report) is "rounded" to the decimal
place I want for that particular database field. I'm trying to standardize on a
particular method as far as possible in order to reduce problems arising from
things like:
Person gets a long statement, adds it up, gets slightly different total because
the figures displayed are formatted, whereas the total summed is formatted also,
but on an overall format rather than a line by line format (if that makes
sense).
And, if exporting, so that the data doesn't have long decimal figures.

I realize no one solution fits everything, but want to standardize, then make
adjustments if needed.

Thanks.

On Fri, 16 Jun 2006 10:23:55 -0700, "Jeff Boyce" wrote:

Josh

Define "best" ... "best" for obtaining what kind of result?

?"Best" as in "most accurate" (according to which rounding rule(s))?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Josh" wrote in message
news
I understand why Acess rounds the way it does (at least somewhat), and
"bankers
rounding", so I'm not asking the basic rounding question. (just one level
up
from basic ;-)

I've done rounding two ways, either Currency for both actual currency
values and
and Double Number values. I've also used MS's SymArith function.
(included at
end of this post, just in case anyone wondered what the function was.)

I'm wondering what the 'best practice' way is. Do you simply use Currency
for
Currency, and not round to 2 decimal places. If so, then someone adding
figures
up and comparing could come up with something different, because doesn't
Access
store 4 digit for currency, which could have a slightly different totaled
amount
than someone adding with excel, for example.

I know some people used to say to just use currency for numbers that are
not
actual currency, and just format the Form or Report to not show the $
sign. That
works, but wouldn't the SymArith function be better?

Like I said, I'm just wondering what the best practice is. The few DB's
I've
done work just fine, just wondering what the pros do.

Thanks, Josh

Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
' Alternately:
' SymArith = Abs(AsymArith(X, Factor)) * Sgn(X)
End Function

Adding or subtracting individual base values will not result in any
discrepancy with manual addition or subtraction when a Currency data type is
employed. Where differences can arise is when computations involving
multiplication or division are done on those values.

Say you have a list of items supplied to a customer to whom you give a
discount. Doing this manually many people would calculate the net price per
item rounded to two decimal places, then add the rounded values to give the
total net price. When Access computes the net values for each item supplied,
however, it rounds the result more precisely to four decimal places. The
total net price produced from the summation of these values might differ from
the manually calculated total net price. The total net price produced by
Access is in most circumstances the more correct one as, by virtue of the
greater precision at the individual transaction level the cumulative rounding
errors which result from manually rounding to two decimal places at each
transaction level are suppressed, which is exactly what the Currency data
type is designed to do.

To many people who have been familiar with the manual approach of rounding
per transaction the result produced by Access seems erroneous as the total
can differ from the sum of the individually rounded amounts. Neither can be
said to be right or wrong, however; it all depends on how its wished to be
done in particular business models. Many small businesses prefer computed
values which mimic the manually calculated ones. It should be noted,
however, that when dealing in large currency amounts the result of cumulative
rounding errors can be significant in terms of monies lost or gained. This
is why rounding methods aimed at balancing out the errors by rounding up or
down in equal proportions were commonly used, the basis of this being that
over a large number of transactions the proportion in each case should
approach 50 per cent, but this does not in itself allow for large variations
in the individual amounts; if the transactions rounded up are mostly large
amounts and the ones rounded down are mostly small amounts there will be a
significant cumulative error even if exactly half round up and half round
down. Again the assumption was that over a large number of transactions the
this would balance itself out and any cumulative error would be insignificant.

For an overview of the many different rounding algorithms see:


http://www.pldesignline.com/howto/sh...leID=175801189


Ken Sheridan
Stafford, England

"Josh" wrote:

I understand why Acess rounds the way it does (at least somewhat), and "bankers
rounding", so I'm not asking the basic rounding question. (just one level up
from basic ;-)

I've done rounding two ways, either Currency for both actual currency values and
and Double Number values. I've also used MS's SymArith function. (included at
end of this post, just in case anyone wondered what the function was.)

I'm wondering what the 'best practice' way is. Do you simply use Currency for
Currency, and not round to 2 decimal places. If so, then someone adding figures
up and comparing could come up with something different, because doesn't Access
store 4 digit for currency, which could have a slightly different totaled amount
than someone adding with excel, for example.

I know some people used to say to just use currency for numbers that are not
actual currency, and just format the Form or Report to not show the $ sign. That
works, but wouldn't the SymArith function be better?

Like I said, I'm just wondering what the best practice is. The few DB's I've
done work just fine, just wondering what the pros do.

Thanks, Josh

Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
' Alternately:
' SymArith = Abs(AsymArith(X, Factor)) * Sgn(X)
End Function

It occurred to me that one of my statements might have been a little
confusing. When I referred to the distortion which might result from an
imbalance in the proportions of high and low individual amounts this applies
when one or other falls one side of the significant digit to which a value is
rounded. This arises with 'headline' rounding to a significant digit to the
left of the decimal point rather than rounding to the nearest cent. As an
example the authority I used to work for had an annual budget of around 500
million GBP, which is of course a rounding to the nearest 100 million of the
actual budget amount. Around half of that figure was one department's
budget. As most departments' budgets were less than 100 million GBP,
however, if the rounding had been done at departmental budget level, most
would have been rounded up and only a couple rounded down. The sum of these
would then have been grossly higher than 500 million. This is an extreme
example of course and no-one in their right mind would do it that way, but it
serves to illustrate the principle.

Ken Sheridan
Stafford, England

Visited the link you provided, interesting, but way over my head. g

I'm just talking about generalties in regarding small business (a small mom &
pop-type store). Just curious as to whether the pro's "generally" use currency
for non-currency situations, when the decimal result required will never be more
than four decimal places, and usuall one or two decimal places.

And, if using Currency, whether for actual currency or non-currency situations,
whether the pros "generally" use a round function every step of the way or not.
(not proper syntax, just an example):
MyRound(field1*field2) / MyRound(field3*field4) rather than
MyRound((field1*field2)/(field3*field4)

I know this has been talked to death over the years, but what the heck, I've
always wondered, so thought I'd ask.

Thanks



On Sat, 17 Jun 2006 16:16:01 -0700, Ken Sheridan
wrote:

Adding or subtracting individual base values will not result in any
discrepancy with manual addition or subtraction when a Currency data type is
employed. Where differences can arise is when computations involving
multiplication or division are done on those values.

Say you have a list of items supplied to a customer to whom you give a
discount. Doing this manually many people would calculate the net price per
item rounded to two decimal places, then add the rounded values to give the
total net price. When Access computes the net values for each item supplied,
however, it rounds the result more precisely to four decimal places. The
total net price produced from the summation of these values might differ from
the manually calculated total net price. The total net price produced by
Access is in most circumstances the more correct one as, by virtue of the
greater precision at the individual transaction level the cumulative rounding
errors which result from manually rounding to two decimal places at each
transaction level are suppressed, which is exactly what the Currency data
type is designed to do.

To many people who have been familiar with the manual approach of rounding
per transaction the result produced by Access seems erroneous as the total
can differ from the sum of the individually rounded amounts. Neither can be
said to be right or wrong, however; it all depends on how its wished to be
done in particular business models. Many small businesses prefer computed
values which mimic the manually calculated ones. It should be noted,
however, that when dealing in large currency amounts the result of cumulative
rounding errors can be significant in terms of monies lost or gained. This
is why rounding methods aimed at balancing out the errors by rounding up or
down in equal proportions were commonly used, the basis of this being that
over a large number of transactions the proportion in each case should
approach 50 per cent, but this does not in itself allow for large variations
in the individual amounts; if the transactions rounded up are mostly large
amounts and the ones rounded down are mostly small amounts there will be a
significant cumulative error even if exactly half round up and half round
down. Again the assumption was that over a large number of transactions the
this would balance itself out and any cumulative error would be insignificant.

For an overview of the many different rounding algorithms see:


http://www.pldesignline.com/howto/sh...leID=175801189


Ken Sheridan
Stafford, England

"Josh" wrote:

I understand why Acess rounds the way it does (at least somewhat), and "bankers
rounding", so I'm not asking the basic rounding question. (just one level up
from basic ;-)

I've done rounding two ways, either Currency for both actual currency values and
and Double Number values. I've also used MS's SymArith function. (included at
end of this post, just in case anyone wondered what the function was.)

I'm wondering what the 'best practice' way is. Do you simply use Currency for
Currency, and not round to 2 decimal places. If so, then someone adding figures
up and comparing could come up with something different, because doesn't Access
store 4 digit for currency, which could have a slightly different totaled amount
than someone adding with excel, for example.

I know some people used to say to just use currency for numbers that are not
actual currency, and just format the Form or Report to not show the $ sign. That
works, but wouldn't the SymArith function be better?

Like I said, I'm just wondering what the best practice is. The few DB's I've
done work just fine, just wondering what the pros do.

Thanks, Josh

Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
' Alternately:
' SymArith = Abs(AsymArith(X, Factor)) * Sgn(X)
End Function

Its quite usual to use the Currency data type for non-currency data. Its
common in many situations to format numbers to two decimal places as this
provides a suitable display precision for many purposes. This is of course
exactly analogous to the display of currency values (unless you are a
currency dealer in which case you'd work to a much higher precision) so it’s
a convenient way of suppressing the cumulative rounding errors which might
otherwise result.

As regards the second point, as I said in my first post, there is no
absolute answer to this. Taking our discounted customers as an example
again, if you round the net price at each transaction step the total net
price for a number of transactions might well differ from the net price if
the rounding were done on the sum of the (unseen) non-rounded values, but is
a more accurate application of the discount ratio to the total gross price.
So neither the vendor nor buyer gain or lose. If each transaction's net
price is rounded and the results summed to give the total net price then the
total net price will in all probability not correctly reflect the discount
applied to the total gross price. So one party gains, the other loses out.
Regardless of this many people feel more comfortable with this as the total
net price at the foot of an invoice will accurately represent the sum of the
individual net prices shown on the invoice. If the discount applies
uniformly to all transactions there is an easy answer to this though, which
is not to expose the individual net prices, but to show only the individual
gross prices and apply the discount to the total. However, if the level of
discount varies between individual transactions, based on the quantity
ordered or the type of product say, then the options are (1) show the
individual net prices and sum them to give the total price, accepting that
the total net price may differ from the sum of the individual net prices as
seen. (2) show the individual net prices and sum the rounded values of them
to give the total net price (3) group the transactions by the discount rate
and apply the discount and round the net price at each group level, summing
the rounded values to give the total net price, or (4) group the transactions
by the discount rate and apply the discount at each group level without
rounding, accepting that the total net price may differ from the sum of the
grouped net prices as seen.

With financial data the choice as to which level to round really rests with
the operator of the business, and the programmer should take the lead from
the client after appraising them of the options available, but with other
types of data the choice is more clear cut. Say you have a series of
measurements, to a precision of 4 decimal places of whatever unit of
measurement you are using, between 10 points along a road. To round each
measurement and sum them to get the distance from point 1 to point 10 would
clearly be wrong and rounding should be done once, on the sum of each
individual measurement, and I would say that this should be adopted as a
general principle in all situations unless there are good business reasons
for rounding at an earlier stage. With currency data the maintenance of good
customer relations may well be a perfectly valid business reason for opting
to round at each transaction level; many people find it hard to grasp that
there can be a money value smaller than the penny in their pocket, and if the
total at the bottom of a list of monetary values differs from the sum of what
they can actually see on the page believe they have been short-changed.

Ken Sheridan
Stafford, England

"Josh" wrote:

Visited the link you provided, interesting, but way over my head. g

I'm just talking about generalties in regarding small business (a small mom &
pop-type store). Just curious as to whether the pro's "generally" use currency
for non-currency situations, when the decimal result required will never be more
than four decimal places, and usuall one or two decimal places.

And, if using Currency, whether for actual currency or non-currency situations,
whether the pros "generally" use a round function every step of the way or not.
(not proper syntax, just an example):
MyRound(field1*field2) / MyRound(field3*field4) rather than
MyRound((field1*field2)/(field3*field4)

I know this has been talked to death over the years, but what the heck, I've
always wondered, so thought I'd ask.

Thanks



On Sat, 17 Jun 2006 16:16:01 -0700, Ken Sheridan
wrote:

Adding or subtracting individual base values will not result in any
discrepancy with manual addition or subtraction when a Currency data type is
employed. Where differences can arise is when computations involving
multiplication or division are done on those values.

Say you have a list of items supplied to a customer to whom you give a
discount. Doing this manually many people would calculate the net price per
item rounded to two decimal places, then add the rounded values to give the
total net price. When Access computes the net values for each item supplied,
however, it rounds the result more precisely to four decimal places. The
total net price produced from the summation of these values might differ from
the manually calculated total net price. The total net price produced by
Access is in most circumstances the more correct one as, by virtue of the
greater precision at the individual transaction level the cumulative rounding
errors which result from manually rounding to two decimal places at each
transaction level are suppressed, which is exactly what the Currency data
type is designed to do.

To many people who have been familiar with the manual approach of rounding
per transaction the result produced by Access seems erroneous as the total
can differ from the sum of the individually rounded amounts. Neither can be
said to be right or wrong, however; it all depends on how its wished to be
done in particular business models. Many small businesses prefer computed
values which mimic the manually calculated ones. It should be noted,
however, that when dealing in large currency amounts the result of cumulative
rounding errors can be significant in terms of monies lost or gained. This
is why rounding methods aimed at balancing out the errors by rounding up or
down in equal proportions were commonly used, the basis of this being that
over a large number of transactions the proportion in each case should
approach 50 per cent, but this does not in itself allow for large variations
in the individual amounts; if the transactions rounded up are mostly large
amounts and the ones rounded down are mostly small amounts there will be a
significant cumulative error even if exactly half round up and half round
down. Again the assumption was that over a large number of transactions the
this would balance itself out and any cumulative error would be insignificant.

For an overview of the many different rounding algorithms see:


http://www.pldesignline.com/howto/sh...leID=175801189


Ken Sheridan
Stafford, England

"Josh" wrote:

I understand why Acess rounds the way it does (at least somewhat), and "bankers
rounding", so I'm not asking the basic rounding question. (just one level up
from basic ;-)

I've done rounding two ways, either Currency for both actual currency values and
and Double Number values. I've also used MS's SymArith function. (included at
end of this post, just in case anyone wondered what the function was.)

I'm wondering what the 'best practice' way is. Do you simply use Currency for
Currency, and not round to 2 decimal places. If so, then someone adding figures
up and comparing could come up with something different, because doesn't Access
store 4 digit for currency, which could have a slightly different totaled amount
than someone adding with excel, for example.

I know some people used to say to just use currency for numbers that are not
actual currency, and just format the Form or Report to not show the $ sign. That
works, but wouldn't the SymArith function be better?

Like I said, I'm just wondering what the best practice is. The few DB's I've
done work just fine, just wondering what the pros do.

Thanks, Josh

Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
' Alternately:
' SymArith = Abs(AsymArith(X, Factor)) * Sgn(X)
End Function

Thanks!
Josh


On Sun, 18 Jun 2006 10:05:02 -0700, Ken Sheridan
wrote:

Its quite usual to use the Currency data type for non-currency data. Its
common in many situations to format numbers to two decimal places as this
provides a suitable display precision for many purposes. This is of course
exactly analogous to the display of currency values (unless you are a
currency dealer in which case you'd work to a much higher precision) so it’s
a convenient way of suppressing the cumulative rounding errors which might
otherwise result.

As regards the second point, as I said in my first post, there is no
absolute answer to this. Taking our discounted customers as an example
again, if you round the net price at each transaction step the total net
price for a number of transactions might well differ from the net price if
the rounding were done on the sum of the (unseen) non-rounded values, but is
a more accurate application of the discount ratio to the total gross price.
So neither the vendor nor buyer gain or lose. If each transaction's net
price is rounded and the results summed to give the total net price then the
total net price will in all probability not correctly reflect the discount
applied to the total gross price. So one party gains, the other loses out.
Regardless of this many people feel more comfortable with this as the total
net price at the foot of an invoice will accurately represent the sum of the
individual net prices shown on the invoice. If the discount applies
uniformly to all transactions there is an easy answer to this though, which
is not to expose the individual net prices, but to show only the individual
gross prices and apply the discount to the total. However, if the level of
discount varies between individual transactions, based on the quantity
ordered or the type of product say, then the options are (1) show the
individual net prices and sum them to give the total price, accepting that
the total net price may differ from the sum of the individual net prices as
seen. (2) show the individual net prices and sum the rounded values of them
to give the total net price (3) group the transactions by the discount rate
and apply the discount and round the net price at each group level, summing
the rounded values to give the total net price, or (4) group the transactions
by the discount rate and apply the discount at each group level without
rounding, accepting that the total net price may differ from the sum of the
grouped net prices as seen.

With financial data the choice as to which level to round really rests with
the operator of the business, and the programmer should take the lead from
the client after appraising them of the options available, but with other
types of data the choice is more clear cut. Say you have a series of
measurements, to a precision of 4 decimal places of whatever unit of
measurement you are using, between 10 points along a road. To round each
measurement and sum them to get the distance from point 1 to point 10 would
clearly be wrong and rounding should be done once, on the sum of each
individual measurement, and I would say that this should be adopted as a
general principle in all situations unless there are good business reasons
for rounding at an earlier stage. With currency data the maintenance of good
customer relations may well be a perfectly valid business reason for opting
to round at each transaction level; many people find it hard to grasp that
there can be a money value smaller than the penny in their pocket, and if the
total at the bottom of a list of monetary values differs from the sum of what
they can actually see on the page believe they have been short-changed.

Ken Sheridan
Stafford, England

"Josh" wrote:

Visited the link you provided, interesting, but way over my head. g

I'm just talking about generalties in regarding small business (a small mom &
pop-type store). Just curious as to whether the pro's "generally" use currency
for non-currency situations, when the decimal result required will never be more
than four decimal places, and usuall one or two decimal places.

And, if using Currency, whether for actual currency or non-currency situations,
whether the pros "generally" use a round function every step of the way or not.
(not proper syntax, just an example):
MyRound(field1*field2) / MyRound(field3*field4) rather than
MyRound((field1*field2)/(field3*field4)

I know this has been talked to death over the years, but what the heck, I've
always wondered, so thought I'd ask.

Thanks



On Sat, 17 Jun 2006 16:16:01 -0700, Ken Sheridan
wrote:

Adding or subtracting individual base values will not result in any
discrepancy with manual addition or subtraction when a Currency data type is
employed. Where differences can arise is when computations involving
multiplication or division are done on those values.

Say you have a list of items supplied to a customer to whom you give a
discount. Doing this manually many people would calculate the net price per
item rounded to two decimal places, then add the rounded values to give the
total net price. When Access computes the net values for each item supplied,
however, it rounds the result more precisely to four decimal places. The
total net price produced from the summation of these values might differ from
the manually calculated total net price. The total net price produced by
Access is in most circumstances the more correct one as, by virtue of the
greater precision at the individual transaction level the cumulative rounding
errors which result from manually rounding to two decimal places at each
transaction level are suppressed, which is exactly what the Currency data
type is designed to do.

To many people who have been familiar with the manual approach of rounding
per transaction the result produced by Access seems erroneous as the total
can differ from the sum of the individually rounded amounts. Neither can be
said to be right or wrong, however; it all depends on how its wished to be
done in particular business models. Many small businesses prefer computed
values which mimic the manually calculated ones. It should be noted,
however, that when dealing in large currency amounts the result of cumulative
rounding errors can be significant in terms of monies lost or gained. This
is why rounding methods aimed at balancing out the errors by rounding up or
down in equal proportions were commonly used, the basis of this being that
over a large number of transactions the proportion in each case should
approach 50 per cent, but this does not in itself allow for large variations
in the individual amounts; if the transactions rounded up are mostly large
amounts and the ones rounded down are mostly small amounts there will be a
significant cumulative error even if exactly half round up and half round
down. Again the assumption was that over a large number of transactions the
this would balance itself out and any cumulative error would be insignificant.

For an overview of the many different rounding algorithms see:


http://www.pldesignline.com/howto/sh...leID=175801189


Ken Sheridan
Stafford, England

"Josh" wrote:

I understand why Acess rounds the way it does (at least somewhat), and "bankers
rounding", so I'm not asking the basic rounding question. (just one level up
from basic ;-)

I've done rounding two ways, either Currency for both actual currency values and
and Double Number values. I've also used MS's SymArith function. (included at
end of this post, just in case anyone wondered what the function was.)

I'm wondering what the 'best practice' way is. Do you simply use Currency for
Currency, and not round to 2 decimal places. If so, then someone adding figures
up and comparing could come up with something different, because doesn't Access
store 4 digit for currency, which could have a slightly different totaled amount
than someone adding with excel, for example.

I know some people used to say to just use currency for numbers that are not
actual currency, and just format the Form or Report to not show the $ sign. That
works, but wouldn't the SymArith function be better?

Like I said, I'm just wondering what the best practice is. The few DB's I've
done work just fine, just wondering what the pros do.

Thanks, Josh

Function SymArith(ByVal X As Double, _
Optional ByVal Factor As Double = 1) As Double
SymArith = Fix(X * Factor + 0.5 * Sgn(X)) / Factor
' Alternately:
' SymArith = Abs(AsymArith(X, Factor)) * Sgn(X)
End Function

You really just have to use currency.

You can choose to force the result to 2 decimal places (round to 2), but
REGARDLESS, YOU MUST use
currency data type. YOU CAN NOT USE double.

eg:

Public Sub TestAdd()
Dim MyNumber As Single
Dim i As Integer

For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub

Here is the actual outpput of the above:
1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1


You can see that after just 7 addtions..already rounding is occuring

and if we add the follwing line of code to the end of the above:

if MyNumber = 10.1 = True then
msgbox "the number is 10.1"
else
msgbox "the number is somthing else"
endif

The above will actuall produce:

the number is something else

So, regardless of what links, what concepts. If you are writing business
applications in Excel, or ms-access, you MUST USE currency data type. Using
double vars can still result in rounding errors. I would not trust that
rounding code you have.

So, has this issue been done to death? Hum, not really, but the simple
answer is that you must use currency data types. If you don't want values
beyond 2 decimal places, then build a function that truncates to 2 decimal
places, and always store that value. But, the rule still remains that you
have to use currency data type, and can NOT use double type if you don't
want any rounding errors.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.members.shaw.ca/AlbertKallal

Ok, sounds good, to use currency wherever Integer won't work.

What round function would you suggest, to always round to 1 or 2 decimal, NOT
using Banker's rounding. So the result could be compared to Excel. I've
only made a few DB programs for a couple of 'very' small business's, and they do
expect the results to match excel.

Looked around your website very briefly, picked up an idea or two for user
interface.

Thanks.


On Tue, 20 Jun 2006 19:59:54 -0600, "Albert D.Kallal"
wrote:

You really just have to use currency.

You can choose to force the result to 2 decimal places (round to 2), but
REGARDLESS, YOU MUST use
currency data type. YOU CAN NOT USE double.

eg:

Public Sub TestAdd()
Dim MyNumber As Single
Dim i As Integer

For i = 1 To 10
MyNumber = MyNumber + 1.01
Debug.Print MyNumber
Next i
End Sub

Here is the actual outpput of the above:
1.01
2.02
3.03
4.04
5.05
6.06
7.070001
8.080001
9.090001
10.1


You can see that after just 7 addtions..already rounding is occuring

and if we add the follwing line of code to the end of the above:

if MyNumber = 10.1 = True then
msgbox "the number is 10.1"
else
msgbox "the number is somthing else"
endif

The above will actuall produce:

the number is something else

So, regardless of what links, what concepts. If you are writing business
applications in Excel, or ms-access, you MUST USE currency data type. Using
double vars can still result in rounding errors. I would not trust that
rounding code you have.

So, has this issue been done to death? Hum, not really, but the simple
answer is that you must use currency data types. If you don't want values
beyond 2 decimal places, then build a function that truncates to 2 decimal
places, and always store that value. But, the rule still remains that you
have to use currency data type, and can NOT use double type if you don't
want any rounding errors.