rvfowler2
asked on
FM - How to force 0s in a Percent Calc in the 100ths column
For our leases, we need to force 0s in two places. I've accounted for the 0 before the dot (as you see below), but can't seem to find a way to add a 0 after the 9. Round with a precision of 2 does not give ta 0 in the 100ths column. Tried number to text with no luck. I need to force a 0 anytime the 100ths column is 0. Any ideas?
The Tenant shall pay 90/100 (0.9%) percent
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry to be a pain, but notice if you put 9 instead of .9 below it gives you (9000) percent instead of (9.00) percent.
Let( theRate = .9 ;
"The tenant shall pay " & theRate * 100 & "/100 (" &
Case( theRate < 1 ; "0" ; Int( theRate ) ) &
left( Mod ( theRate ; 1 ) & "00" ; 3 ) & " percent)"
)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Because of the way the percent is auto entered from another field, it is set as a decimal, but with an entry of 2 decimal places in the Inspector tab. The export also has checked for it to "export data as formatted" so I thought it would work. Maybe having to divide by 100 resets it, but even when I used a round function set at 2, it still omits the final 0. But I like your custom function and will give it a try.