How to show a number as a percentage

[paul_ripley]

Hello

I have an expression (see below) that calculates a number.

=NUM(round(
(If(floor(sum({$<period ={">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, pl = {F01}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1 = 0, '-',
floor(sum({$<period ={">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, pl = {F01}>} IS_Investment_GBP) +500000,1000000)/1000000 *-1 )
-
If(sum({$<period = {">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, bud_account = {IS3}>} bud_amt ) = 0, '-',
sum({$<period = {">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, bud_account = {IS3}>} bud_amt) )
)
/

If(sum({$<period = {">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, bud_account = {IS3}>} bud_amt ) = 0, '-',
sum({$<period = {">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, bud_account = {IS3}>} bud_amt) )
*100
),'#,##0;(#,##0)')


For example, the above can give 14 or (14) but what I would like it to show is 14% or (14%)

When I change the last line to

),'#,##0%;(#,##0%)')

I get 1400% or (1400%)

Can ayone explain how I can get it to show 14% or (14%)

Many thanks

Paul

[YoussefBelloum]

Hi,

Divide the whole expression by 100

your_expression(...)/100

[sunny_talwar]

or in your case, do not multiply by 100

[Peter_Cammaert]

Percentage formatting assumes that your numbers are between 1.0 (100%) and 0.0 (0%). A velua of 14 then becomes... a lot.

This happens for both percentage format strings as well as for situations where you mark the "Show in Percentage (%)" checkbox on the Properties->Number tab.

As I mentioned in your other discussion, you might do away with the Num() call if you use the options on the Number tab instead of explicit formatting in your expressions. Makes expressions less complex.

[paul_ripley]

Yes unfortunately I have to present the numbers in N Printing so was hoping to be able use a formula, that's why I have been using NUM.

[paul_ripley]

Thanks for all your help.  I think the issue was a combination of the rounding and the *100.  When I remove the *100 the percentage of 14% becomes 0.14 and then the ROUND function rounded it down to zero

[Peter_Cammaert]

It will work with a formula, but you'll need to get your values to range from 0.0 to 1.0. That's why the other perfectly valid suggestions try to convince you to divide by 100 or omit a multiplication by 100.