11 Replies Latest reply on Nov 2, 2016 5:15 PM by Ivan Young

# Truncating instead of rounding a number

Hi, I would like to truncate a number to the first two decimals. For example, 10.8299 it would be 10.82.

In excel I would do TRUNC(10.8299)= 10.82.

ROUND FUNCTIONS is not an option because TRUNC(10.8299)= 10.83.

The filed "value" have positive a negative numbers

I would appreciate some help,

Thanks a lot!

• ###### 1. Re: Truncating instead of rounding a number

Hi Frederico,

Not sure if i am missing something but would the following not be sufficient.

or must it really be as depicted 10.82 then I would try something like MID(str([sales]),1,5) if applicable in your scenario.

1 of 1 people found this helpful
• ###### 2. Re: Truncating instead of rounding a number

Federico,

• ###### 3. Re: Truncating instead of rounding a number

Hi Norbet, thanks for your time

What I need is a conversion, not to change the format

Your approach of MID([Value]) is good but you have to consider:

1) You can not use MID([Value]). MID Function is for string

2) Thinking togheter.. if you use LEFT(STR([Value]), X) you have to take care of the number of characters that the Field Value has.

Also, we have consider [Value] has positive a negative numbers.

Then I find this solution (but I do not think that is the more efficent way):

IF LEN(STR(INT([Value])))=1 then FLOAT(LEFT(STR([Value]), 4)) ELSEIF

LEN(STR(INT([Value])))=2 then FLOAT(LEFT(STR([Value]), 5)) ELSEIF

LEN(STR(INT([Value])))=3 then FLOAT(LEFT(STR([Value]), 6)) ELSEIF

LEN(STR(INT([Value])))=4 then FLOAT(LEFT(STR([Value]), 7)) ....

...

end

(if you have possitive and negative numbers you should add a character to the negatives values)

• ###### 4. Re: Truncating instead of rounding a number

Federico,

Did you examine approach provided by Shawn Wallwork alread based on FLOOR and CEILING function?

• ###### 5. Re: Truncating instead of rounding a number

Federico,

Did a little testing on your scenario and find my approach below and stored in workbook version 9.3

The following is visible.

a. You can use string only when you are displaying data on the lowest level of the data in your set

b. neg pos and amount can be solved by formula like this

if [value]<=0 then mid(str([value]),1,6)

elseif [value]<10 then "   "+mid(str([value]),1,4)

else " "+mid(str([value]),1,5) END

• ###### 6. Re: Truncating instead of rounding a number

You guys are right. I was testing this incorrectly. So deleted comments. Sorry.

--Shawn

• ###### 7. Re: Truncating instead of rounding a number

Norbert, the problem with that solution is that "Convert" is a STRING here. You should add FLOAT(STR(....)).

Also, considering yo have the value= -2.1238. Also, if value take more digits our approch will become a headache.

We should vote for a TRUNC functions. Anyone know if the idea is already open to vote?

1 of 1 people found this helpful
• ###### 8. Re: Truncating instead of rounding a number

This formula works better for me. Of course, if you have numbers with 4 digits you add a new row with a similar expression

IF [Value]>=0 THEN

IF LEN(STR(INT([Value])))=1 then FLOAT(LEFT(STR([Value]), 4)) ELSEIF

LEN(STR(INT([Value])))=2 then FLOAT(LEFT(STR([Value]), 5)) ELSEIF

LEN(STR(INT([Value])))=3 then FLOAT(LEFT(STR([Value]), 6))

END

ELSEIF [Value]<0 THEN

IF LEN(STR(INT([Value])))=2 then FLOAT(LEFT(STR([Value]), 5)) ELSEIF

LEN(STR(INT([Value])))=3 then FLOAT(LEFT(STR([Value]), 6)) ELSEIF

LEN(STR(INT([Value])))=4 then FLOAT(LEFT(STR([Value]), 7))

END

END

1 of 1 people found this helpful
• ###### 9. Re: Truncating instead of rounding a number

Federico,

I realize it's far from  "ideal" but in current situation...... I don't know if the "TRUNC" functions as desired is up for voting.

I did a google search and can't find anything you are desiring.

• ###### 10. Re: Truncating instead of rounding a number

Of course It helps, but take care to consider all the options that value can take.

It was a pleasure to share ideas and thanks again for your time

1 of 1 people found this helpful
• ###### 11. Re: Truncating instead of rounding a number

Hi Fed,

Depending on your datasource the FLOOR function might be available.  Here is an example using floor.  I named our single field [Trunc Me]

FLOOR([Trunc Me]*100)/100

Regards,

Ivan

1 of 1 people found this helpful