-
1. Re: Truncating instead of rounding a number
Norbert Maijoor
Nov 2, 2016 7:03 AM
1 of 1 people found this helpfulHi 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.
-
-
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
Norbert Maijoor
Nov 2, 2016 7:28 AM
Federico,
Did you examine approach provided by Shawn Wallwork alread based on FLOOR and CEILING function?
-
5. Re: Truncating instead of rounding a number
Norbert Maijoor
Nov 2, 2016 8:03 AM
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
1 of 1 people found this helpfulNorbert, 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?
-
8. Re: Truncating instead of rounding a number
1 of 1 people found this helpfulThis 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
-
9. Re: Truncating instead of rounding a number
Norbert Maijoor
Nov 2, 2016 9:00 AM
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
1 of 1 people found this helpfulOf 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
-
11. Re: Truncating instead of rounding a number
1 of 1 people found this helpfulHi 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
