
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to round a Negative number to 0
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
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be try this
=Num(Ceil(Exp1 - Exp2, 0.01), '#,##0%')


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this
ROUND(Num(Exp1,'###%') - Num(Exp2,'###%'))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How about this
=Num(Ceil(Exp1 - Exp2, 0.005), '#,##0%')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this 🙂
=Num(Round('1.2%', 0.005), '#,##0%')


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Maybe a to negative values extended formatting might be useful, like:
=Num(Exp1 - Exp2,'#,##0%;#,##0%')
- Marcus

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Again this worked for that particular scenario lol but now even if the difference is 0.3%, I am getting 1%.

- « Previous Replies
-
- 1
- 2
- Next Replies »