Sum of two Currency Formulas

[opo]

I have several reports where i calc a totals of two or more formula that have a currency format. The totals are sometimes off by a penny
such as $5.64 + $7.38 $13.00 instead of $13.01
What can i do?

 

[kenhamady]

Currency format might round for display, but doesn't mean that the underlying value has been rounded. Use the round function in the formula to round to 2 decimal places, and then the sum will tie. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[FoxG]

Hi Ken,

Although this will give the right results for the print, it may give incorrect results if this value is then used in subsequent calculations. These sort of rounding errors have to be expected when producing reports even if they do look a bit strange!

Geoff

 

[kenhamady]

Geoff,

Allow me to disagree.
If you include the round() function in the formula, and then use the formula in subsequent calculations and for display purposes, there will be no rounding error in either the subsequent calculations or in the printout. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[FoxG]

Hi Ken,

I agree that this will avoid compounding rounding errors,
but surely

0.0045 + 0.0045 = 0.0090 * 99.0000 = 0.0891
will display as...

0.005 + 0.005 = 0.010 * 99.000 = 0.089 3dp ???
0.00 + 0.00 = 0.01 * 99.00 = 0.09 2dp ???

And hence utter panic when the manager says the computer doesn't work !!

If you truncate its gets even better...

0.00 + 0.00 = 0.00 * 99.00 = 0.08 !!!


Maths is a wonderful thing...
Geoff

 

[kenhamady]

Geoff,

I think you are confusing accuracy with precision. Using the Round() function in a formula allows you to control when the rounding happens and how it happens.

Rounding, because it adjusts the number, allways reduces precision, but that is the point. You get to decide when a less precise answer is actually more accurate.

For example, if I buy an item for 49 cents and have a 6% tax, the precise tax is .0294, but the accurate tax is .03 since we can't split pennies. I can use the round function to get the tax to return .03, and then when I total or use the tax figure in another formula, it remains exactly .03.

It is up to you to decide when the rounding should occur to make things more accurate. I assume that opo is showing the accurate values, and that the hidden fractions are unnedded. If OPO rounds his two values, or their column, to 2 decimal places BEFORE he adds them up, he will get an accurate total. And this total will not have rounding errors if used in further calculations. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/