[Shawn O'Connor]

When doing certain calculations in word, such as a percent I get an answer
that is technically correct, but ends up carrying forward decimal values that
ultimately lead to an incorrect displayed result. For example, if I wish to
calculate 20% off of some dollar amount -- say $24.99 I get 19.992. OK,
fine...except that if I carry forward that calculated value in another field
as a value for another equation that extra .002 throws the calculated value
off. It's a few pennies different than if I had just used 19.99 as the basis
for the calculation. For example, If I say $19.992 * 12 that equals 239.90,
but if I use 19.99 then the value is 239.88. I know, splitting hairs, but
when your published number is $19.99 the value should be exact.

I know in Excel there is a preference one can select to "set precision as
displayed" to handle this exact problem. Is there something like that in
Word? Or some other way around this problem? -- Thanks.

[Graham Mayor]

See fellow MVP Macropod's excellent work on math calculations in Word which
you can download from http://www.gmayor.com/downloads.htm#Third_party

With regard to your example - you can control exactly what you calculate by
using formatting switches eg

{ =24.99 * .8 \# "$,0.00"}
results in $19.99

{ =24.99 * .8 \# "$,0.000"}
results in $19.992

{ ={ =24.99 * .8 \# "0.00"} * 12 \# "$,0.00" }
results in $239.88

{ ={ =24.99 * .8 \# "0.000"} * 12 \# "$,0.00" }
results in $239.90


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Shawn O'Connor wrote:
When doing certain calculations in word, such as a percent I get an
answer that is technically correct, but ends up carrying forward
decimal values that ultimately lead to an incorrect displayed result.
For example, if I wish to calculate 20% off of some dollar amount --
say $24.99 I get 19.992. OK, fine...except that if I carry forward
that calculated value in another field as a value for another
equation that extra .002 throws the calculated value off. It's a few
pennies different than if I had just used 19.99 as the basis for the
calculation. For example, If I say $19.992 * 12 that equals 239.90,
but if I use 19.99 then the value is 239.88. I know, splitting
hairs, but when your published number is $19.99 the value should be
exact.

I know in Excel there is a preference one can select to "set
precision as displayed" to handle this exact problem. Is there
something like that in Word? Or some other way around this problem?
-- Thanks.

[Shawn O'Connor]

I'll have to test that out, but at first blush I don't think your solution
will work. Formatting the data may not necessarily change the precision that
the calculation uses. In other words, while the Word field may be formatted
to only show # "$,0.00 or $19.99, if the calculated value is 19.992 then the
other calculation for which this value is an input will use the 19.992 value
and not the displayed value --hence a result that is slightly off.

I did find a solution though -- perhaps there is a better one out there and
I may find that further testing causes other problems, but implementing a
rounding rule in the formula so that the calculated value will only report
19.99 (round down) and the display value becomes the same. That seemed to do
the trick.

"Graham Mayor" wrote:

See fellow MVP Macropod's excellent work on math calculations in Word which
you can download from http://www.gmayor.com/downloads.htm#Third_party

With regard to your example - you can control exactly what you calculate by
using formatting switches eg

{ =24.99 * .8 \# "$,0.00"}
results in $19.99

{ =24.99 * .8 \# "$,0.000"}
results in $19.992

{ ={ =24.99 * .8 \# "0.00"} * 12 \# "$,0.00" }
results in $239.88

{ ={ =24.99 * .8 \# "0.000"} * 12 \# "$,0.00" }
results in $239.90


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Shawn O'Connor wrote:
When doing certain calculations in word, such as a percent I get an
answer that is technically correct, but ends up carrying forward
decimal values that ultimately lead to an incorrect displayed result.
For example, if I wish to calculate 20% off of some dollar amount --
say $24.99 I get 19.992. OK, fine...except that if I carry forward
that calculated value in another field as a value for another
equation that extra .002 throws the calculated value off. It's a few
pennies different than if I had just used 19.99 as the basis for the
calculation. For example, If I say $19.992 * 12 that equals 239.90,
but if I use 19.99 then the value is 239.88. I know, splitting
hairs, but when your published number is $19.99 the value should be
exact.

I know in Excel there is a preference one can select to "set
precision as displayed" to handle this exact problem. Is there
something like that in Word? Or some other way around this problem?
-- Thanks.

[djprius]

Shawn,

If you had looked closely at Graham's reply, you would *not* have
said "... at first blush I don't think your solution will work." He
gave you the proof right there in his reply: examples #3 and #4
demonstrate the different RESULT (not just display); in #3, it is
$239.88 while in #4, it is $239.90.

So, you can test all you want, but all you'll do is see that Graham,
an expert, did all the work for you.

(I don't question your concern whether the formatting advice affected
only 'display' or whether it performed 'rounding'. Only that careful
examination of the reply addressed your concern.)


David


************************************************



Shawn O'Connor wrote:
I'll have to test that out, but at first blush I don't think your solution
will work. Formatting the data may not necessarily change the precision that
the calculation uses. In other words, while the Word field may be formatted
to only show # "$,0.00 or $19.99, if the calculated value is 19.992 then the
other calculation for which this value is an input will use the 19.992 value
and not the displayed value --hence a result that is slightly off.

I did find a solution though -- perhaps there is a better one out there and
I may find that further testing causes other problems, but implementing a
rounding rule in the formula so that the calculated value will only report
19.99 (round down) and the display value becomes the same. That seemed to do
the trick.

"Graham Mayor" wrote:

See fellow MVP Macropod's excellent work on math calculations in Word which
you can download from http://www.gmayor.com/downloads.htm#Third_party

With regard to your example - you can control exactly what you calculate by
using formatting switches eg

{ =24.99 * .8 \# "$,0.00"}
results in $19.99

{ =24.99 * .8 \# "$,0.000"}
results in $19.992

{ ={ =24.99 * .8 \# "0.00"} * 12 \# "$,0.00" }
results in $239.88

{ ={ =24.99 * .8 \# "0.000"} * 12 \# "$,0.00" }
results in $239.90


--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org




Shawn O'Connor wrote:
When doing certain calculations in word, such as a percent I get an
answer that is technically correct, but ends up carrying forward
decimal values that ultimately lead to an incorrect displayed result.
For example, if I wish to calculate 20% off of some dollar amount --
say $24.99 I get 19.992. OK, fine...except that if I carry forward
that calculated value in another field as a value for another
equation that extra .002 throws the calculated value off. It's a few
pennies different than if I had just used 19.99 as the basis for the
calculation. For example, If I say $19.992 * 12 that equals 239.90,
but if I use 19.99 then the value is 239.88. I know, splitting
hairs, but when your published number is $19.99 the value should be
exact.

I know in Excel there is a preference one can select to "set
precision as displayed" to handle this exact problem. Is there
something like that in Word? Or some other way around this problem?
-- Thanks.