Odd results from the function floor()

[Anonymous]

I have values in a chart where I do not want the results to round past .0001.  I am using the Floor() function to drop values past this value. In most cases it works as I would expect but not always. 

The formula being used is  floor(TotalPointsEarned/TotalPointsPossible,.0001)

Notice the first record and how it takes the 7/10 = .7 and turns it into .6999 but in all other cases it works properly. 

TotalPointsEarned	TotalPointsPossible	TotalPointsEarned/TotalPointsPossible	floor(TotalPointsEarned/TotalPointsPossible,.0001)
		-	-
7	10	0.7	0.6999
9	11	0.818181818	0.8181
10	11	0.909090909	0.9090
10.75	12	0.895833333	0.8958
9.75	13	0.75	0.7500

Thanks for your help.

[settu_periasamy]

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

[Anonymous]

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.

[Anonymous]

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.

[settu_periasamy]

Hi,

Did you try with num?  like

=Num(.8432,'#,##0.00%')

[jonathandienst]

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 12-14th 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.

[Anonymous · Accepted solution]

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

[Anonymous]

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.