×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Evaluate number of characters in formula to suppress decimals when needed2

## Evaluate number of characters in formula to suppress decimals when needed

(OP)
I have a formula called Sales_Total that, at times, can exceed 11 characters in length but the cell width only allows for 11 characters. When this happens, the report returns ######## instead of the amount. What I'd like to do is evaluate the number of characters in the total and if the character length is greater than or equal to 12, display the number without the decimals. So if my total is 123456789.00, which currently returns ########, I want to suppress the .00 so it will show 123456789. If the number is less than or equal to 11, I want it to include the decimals. So if the total is 12345.00, I want it to show 12345.00.

I tried the following in a formula called Sales_Total (LEN) but it's always suppressing the decimals, regardless of the length, and I can't figure out why.

if len(totext({@Sales_Total})) >= 12 then totext({@Sales_Total},0,"") else
if len(totext({@Sales_Total})) < 12 then totext({@Sales_Total},0.00,"")

### RE: Evaluate number of characters in formula to suppress decimals when needed

replace 0.00 with 2

### RE: Evaluate number of characters in formula to suppress decimals when needed

This should work but a neater solution is to use number formatting.

if len(totext({@Sales_Total}, 2,"")) >= 12 then totext({@Sales_Total},0,"") else
if len(totext({@Sales_Total}), 2, "") < 12 then totext({@Sales_Total},2,"")

Right click your formula in design mode, select Customise.
Click formula box next to decimal place

If @Sales_Total >= 100000000 then 0 else 2

Ian

### RE: Evaluate number of characters in formula to suppress decimals when needed

(OP)
Awesome ty both!!!

### RE: Evaluate number of characters in formula to suppress decimals when needed

(OP)
Hey Ian. I should've tested your number formatting formula first. Having some issues with it. I like this solution better as I don't have to create another formula.

I've tried the following and I'm still getting #########...

If {@Sales_Total} >= 100000000 then 0 else 2

I also tried...

If {@Sales_Total} > 9999999.99 then 0 else 2

### RE: Evaluate number of characters in formula to suppress decimals when needed

It worked fine when I tried it with a big number.

Widen your details section and move the field down and then lengthen field so that ##### disappears.
What do you see?
If decimals have gone then you may have to reduce font size.

Ian

### RE: Evaluate number of characters in formula to suppress decimals when needed

(OP)
The number I currently see when I widen the field is 2121205.49

### RE: Evaluate number of characters in formula to suppress decimals when needed

(OP)
I kept working and this worked. Thanks!

If {@Sales_Total} > 999999 then 0 else 2

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!