

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Round up to 2 decimals, but ONLY to Fractions, NOT to Integers - HOW?
Hi All,
I have a Text Box showing a value calculated by 2 variables. For example var A + var 2.
For Id =100, var A + var B = 35
For Id =200, var A + var B = 20.5
For Id =300, var A + var B = 40.275
For Id =400, var A + var B = 15.25
Requirement:
The result should be rounded up to 2 Decimal places, but only for values/results having 2 or more decimal places. I don't need ZERO after decimals.
For example, my expected output would be
For Id =100, var A + var B = 35 (not 35.00)
For Id =200, var A + var B = 20.5 (not 20.50)
For Id =300, var A + var B = 40.28
For Id =400, var A + var B = 15.25
I tried using NUM( 'var A + var B , '#.##') Can this be achieved in some other ways?
Regards!
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use the below in a text box object (replace Num with your number or variable)
If you are going to round anything more than 2 decimal point (4.675 -> 4.68 and 4.673 -> 4.67) then use this:
=If(frac(Num*10) > 0, Num(Round(Num, 0.01), '#,##0.00'), If(frac(Num)>0, Num(Num, '#,##0.0'), Num(Num, '#,##0')))
If you are just looking for formatting (4.675 -> 4.67 and 4.673 -> 4.67) then try this:
If(frac(Num*10) > 0, Num(Num, '#,##0.00'), If(frac(Num)>0, Num(Num, '#,##0.0'), Num(Num, '#,##0')))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Try like this:
Num(var A+var 2),'##.00') ,It wil give you only two decimal points.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Use round( x [ , base [ , offset ]]) . Find the screenshot it will help.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this:
=If(frac(FieldName*10) > 0, Num(FieldName, '#,##0.00'), If(frac(FieldName)>0, Num(FieldName, '#,##0.0'), Num(FieldName, '#,##0')))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
In the script:
Table:
LOAD *,
If(frac(Num*10) > 0, Num(Num, '#,##0.00'), If(frac(Num)>0, Num(Num, '#,##0.0'), Num(Num, '#,##0'))) as NewNum,
If(frac(Num*10) > 0, Num(Round(Num, 0.01), '#,##0.00'), If(frac(Num)>0, Num(Num, '#,##0.0'), Num(Num, '#,##0'))) as NewNum1;
LOAD * Inline [
Num
35
20.5
40.275
15.25
18.273
];
Output:


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunindia,
Thanks for the quick help.
I am using the expression in a Text Object, using variable (Not in Load Script). Will the above script help?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use the below in a text box object (replace Num with your number or variable)
If you are going to round anything more than 2 decimal point (4.675 -> 4.68 and 4.673 -> 4.67) then use this:
=If(frac(Num*10) > 0, Num(Round(Num, 0.01), '#,##0.00'), If(frac(Num)>0, Num(Num, '#,##0.0'), Num(Num, '#,##0')))
If you are just looking for formatting (4.675 -> 4.67 and 4.673 -> 4.67) then try this:
If(frac(Num*10) > 0, Num(Num, '#,##0.00'), If(frac(Num)>0, Num(Num, '#,##0.0'), Num(Num, '#,##0')))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Good one sunindia
Nice trick


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Sunindia,
Thanks again for this.
I have applied the first formula in above post.
However I have some values like 7.5, 35.5.... For these, the result coming as 7.50, 35,50.
Seems the second If Condition is not getting applied properly. Please help!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi DMohanty
Could you please attach the sample file. It will help the folks to give better solutions.

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