How to round a Negative number to 0

[pranaview]

Hi Guys,

I'm stuck in a small problem. I am getting a difference of two expressions(both expressions results in percentage values) and I need to show whether the result is > 0 or < 0 or = 0 in percentage. 

So, for example if Expression 1 gives me 98% and Expression 2 gives me 97% which shows a decrease of 1% and I will show it as -1%. Or If I have 98% and 98.8% which gives me an increase of 0.8% but I'm rounding it up and showing as 1%, it's working fine till here.

But now suppose two expressions gives me values such as 97% and 96.8% which means a decrease of 0.2 % but I have to show it as 0% only but What i'm getting with my current expression is -0% which doesn't make any sense to the user. 

Current expression :

=Num(Num(Exp1,'###%') - Num(Exp2,'###%'),'#,##0%')

I need to get this done asap so any suggestions will be hugely appreciated

Cheers,

Pranav

[pradosh_thakur]

Hi

Is the round fumction not working for you

you can try like this

round(Exp1-Exp2) & '%'

[marcus_sommer]

Without diving deeper in the case I'm not sure if there is really a single solution possible or if the requirements are at all contrary to eachother - therefore your solution might be to implement one or two conditions and then branching into different roundings/formattings.

- Marcus

[pranaview]

Hi Pradosh,

No, The Round function is not working in my case. Getting 0 when I should not be getting 0.

Thanks!

[sunny_talwar]

0.3 should round to 0.5%, right? That is what it was doing, it is just your Num function which threw it off... try this

=Num(Round('0.3%', 0.005), '#,##0.0%')

[pranaview]

Sunny, actually I only want integer values, so for 0.3% case, it should simply be rounded down to 0% and if it's 0.5% - 0.9% then to 1%. Similarly on negative side of spectrum, if it is -0.3%, I need to show 0% and if it's -0.6% then I gotta show -1%.

Hope this clears my requirement.
Thanks!

[sunny_talwar · Accepted solution]

Try this

=Num(Round(Number, 0.01), '#,##0%')

[pranaview]

This worked on all possible scenarios I could try. Looks like i got my solution.

Thanks a lot Sunny!!