Here the problem raises with rounding the two decimal values. But i need like using trunc in custom formatting. I need the values not to get rounded with decimal but display as it was.
Problem 1
[>=1000000]$#0.00,, "M";[>=100000]$#0.00, "K";$#.00
this code converts millions and K with 2 decimals but it is rounding the first two digits
Values:106,008,784.41 69158976 36849808.41
when we divide by million we get the below values
<colgroup><col width="167"><col width="147"><col width="150"></colgroup><tbody>
</tbody>
Expected: 106.00 69.15 36.84
Actual: 106.01M 69.16M 36.85M (result of my code)
Problem 2
[>0] +##%;[<0]-##%;0
this code works as if it greater than 0 say 1, then display as +100% , lesser than 0 say -1 then -100% else 0
but it goes without values, when between (0.009 , -0.009) resulting like + % or -%. Say If the values between (0,-0.009) then resulting -%
( to simply the problem occurs when it is lesser then +or- 1 percentage)
So i need it to round 0 if the values between (+0.9,-0) and (0,-.9)
Suggestions , codes are welcome, thanked and highly appreciated.
Thanks in advance.
venky
Problem 1
[>=1000000]$#0.00,, "M";[>=100000]$#0.00, "K";$#.00
this code converts millions and K with 2 decimals but it is rounding the first two digits
Values:106,008,784.41 69158976 36849808.41
when we divide by million we get the below values
106.0087844 | 69.158976 | 36.84980841 |
<colgroup><col width="167"><col width="147"><col width="150"></colgroup><tbody>
</tbody>
Expected: 106.00 69.15 36.84
Actual: 106.01M 69.16M 36.85M (result of my code)
Problem 2
[>0] +##%;[<0]-##%;0
this code works as if it greater than 0 say 1, then display as +100% , lesser than 0 say -1 then -100% else 0
but it goes without values, when between (0.009 , -0.009) resulting like + % or -%. Say If the values between (0,-0.009) then resulting -%
( to simply the problem occurs when it is lesser then +or- 1 percentage)
So i need it to round 0 if the values between (+0.9,-0) and (0,-.9)
Suggestions , codes are welcome, thanked and highly appreciated.
Thanks in advance.
venky