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

[sunny_talwar]

May be try this

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

[pradosh_thakur]

Try this

ROUND(Num(Exp1,'###%') - Num(Exp2,'###%'))

[pranaview]

Hi Sunny,
Thanks for your response. This solution works in the scenario where i'm getting -0% but suppose if I am getting -4% as the difference then it's making that 0% as well.
Any idea why or what else i can try here?

Thanks!

[sunny_talwar]

How about this

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

[pranaview]

Sunny, this solution is working in the above scenarios but now even if the difference is 1.2%, it is making it 2% which is not the right way to display, it should be 1% unless it's > 1.5%

[sunny_talwar]

Try this 🙂

=Num(Round('1.2%', 0.005), '#,##0%')

[marcus_sommer]

Maybe a to negative values extended formatting might be useful, like:

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

- Marcus

[pranaview]

Hi Marcus,

I don't want to get rid of negative sign altogether. I just don't want it to get displayed when the value is < -0.5% cos i'm making it 0% and it's coming as -0%. If it is suppose -0.8%, I have to display it as -1% but with your solution it becomes 1%.

Maybe I should have been more clearer in the Post Header.

Thanks!

[pranaview]

Hey Sunny,

Again this worked for that particular scenario lol but now even if the difference is 0.3%, I am getting 1%.