Crystal Formual & Excel = Different Results

[aj3221]

I have a formula in crystal and it’s not matching the result I’m getting in excel.

Excel Results:
Cost: $6.29
Sell: $8.89
Margin: 29.25%

Excel:
I'm figuring this by sell-cost = diff then the diff/sell

Crystal Results:
Cost: $6.29
Sell: $8.89
Margin: 29.23%

Crystal –
if {db.Cost} = 0 then 0 else if
{db.Sell} = 0 then 0 else
({db.sell}-{db.cost}) / ({db.sell)*100

I want crystal to display 29.25%. I know it has to be rounding or the decimal places maybe but can't figure it out

Thank you!

 

[SkipVought]

hi,

But actually it is a matter of ROUNDING.

The value in Excel is 29.24634421

If its rounded up to 2 places then you get 29.25

If its rounded down to 2 places its 29.24

I can't see why it would be rounded to .23????

Unless you also truncate in some way, the ACTUAL VALUE IS 29.24634421 to the application precision.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MCuthill]

aj:

Further to Skip's post - perhaps your fields themselves have more than 2 decimal places as well. You could try enclosing your fields with Round() functions to test. Alternately, expand all columns to more decimals and see if there is another cause (posting the results of said expansion should you still have questions).

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[fisheromacse]

i would second Mike's post.
My first suggested culprit would be the original data. It is possibly not really XX.XX, but XX.XXXXXX.
For example, if the original data is really 6.29 and 8.888 then the result would be 29.2304