
Re: Odd results from the function floor()
Settu Periyasasamy Sep 22, 2015 10:44 PM (in response to Darrin Pilkington)Yes you are right. In excel it gives 0.7 for Floor(0.7,0.0001).
Check the below link..
Doubts in understanding the ceil and floor functions
Not sure about this. But, check this..
In this case (in QV), the multiple of 0.0001 are
0.0001
0.0002
...
...
0.6999
0.7

Book2.xlsx 149.5 K

Re: Odd results from the function floor()
Darrin Pilkington Sep 23, 2015 1:29 AM (in response to Settu Periyasasamy )Settu,
Thank you for the response. The link to the article really helped explain what is really happening with ceil() and floor().
But I still had the problem of dropping any values after the 4th position due to the team not wanting rounding past the 100th percent.
At random I tried left(TotalPointsEarned/TotalPointsPossible, 6) and that dropped off the remaining values to cure the rounding. I am really surprised this worked as I thought it was only design for text.


Re: Odd results from the function floor()
Darrin Pilkington Sep 23, 2015 1:42 AM (in response to Darrin Pilkington)OK,
Maybe I spoke too soon. Now I am really confused.
In a chart left(TotalPointsEarned/TotalPointsPossible, 6) gives a value of .7 but that same formula in the load script returns a value of .6999
All I am trying to do is cut off the last values to the 100th place as a percent without rounding.
So .8432684 would cut off the 684 leaving .8432 and then I format as 84.32%
In addition .7 would stay .7 and finally format as 70%
Is there some other function that would trim off the last values without rounding or dropping to some next closest value?
Thank you.

Re: Odd results from the function floor()
Settu Periyasasamy Sep 23, 2015 1:53 AM (in response to Darrin Pilkington)Hi,
Did you try with num? like
=Num(.8432,'#,##0.00%')

Re: Odd results from the function floor()
Jonathan Dienst Sep 23, 2015 2:08 AM (in response to Darrin Pilkington)This is rounding issue caused by the fact that 0.7 cannot be exactly represented in the binary scheme used in ISO standard floating point numbers used by QV. Much like 1/3 cannot be exactly represented in base 10  0.3333 is an approximation, no matter how many decimal places you use.
Normally this error is small and not significant (usually in the 1214th decimal place), but here the floor function is rounding down to 0.6999 because the actual number is minutely smaller than .7. You will see this with other numbers that cannot be exactly represented such as 0.6.

Re: Odd results from the function floor()
Darrin Pilkington Sep 23, 2015 2:14 AM (in response to Jonathan Dienst )Jonathan,
It's funny you mention 0.6. That was the other instance where this was happening.
As you mentioned, typically this is not a factor but this relates directly to agents scorecards and we have set the cutoff with no rounding as it can push them up into a higher scored category. So the actual cutoff value here is important.
Thank you for explaining why this was happening.



Re: Odd results from the function floor()
Darrin Pilkington Sep 23, 2015 2:09 AM (in response to Darrin Pilkington)I did try that and it rounds the value up.
For example .82828282 becomes 82.83%
So this is interesting and odd. I am not sure if something was being held in memory but I cleared all data from the app and then reran again with the left(TotalPointsEarned/TotalPointsPossible, 6) and it gave me the same value of .7 that the formula in chart did.
It's interesting, I searched throughout the community and did not see anything about the left() function on a number to trim off the trailing values.
Thanks again Settu