Percentages in Crystal Reports vs Excel

Wednesday 19 May 2010 @ 11:47 am

Percentages in Crystal behave slightly differently than they do in Excel.  Say you calculate 13/21 and get .619.   In Excel you can click the percent symbol on the toolbar and get the value to show as  61.9%.  And if you multiply another value by this you will still by multiplying by .619.

In Crystal, clicking on the % on the toolbar does not move the decimal over for display.  So if you have the same calculation of .619 and you click the percent symbol it will simply add the symbol, showing as .619%.   If you want the displayed value to shift the decimal you have to change the calculation.  One way is to use:

13 % 21 //(substituting the percent symbol for the slash in the calculation).

This will result in a number that is 100 times larger, or 61.9.  Then you can add the symbol using the toolbar. Some folks keep the slash and multiply the result by 100, which does the exact same thing.

If you use a percentage formula within another formula keep in mind that the CR decimals are “what you see is what you get”.  So if you multiply by a value that shows 61.9% you are actually multiplying by 61.9 not .619 (Excel will use .619).  In other words, the percent symbol in preview means nothing to Crystal calculations.

One last tangentially related note.  Don’t accidentaly use a backward slash as a divide operator.  This is a valid operator that does something called an “integer divide” which I have discussed before.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro