ROUNDUP and ROUNDDOWN function

I am using the following function to try to calculate some sales competition points and it is working in reverse.

=IF((N4-0.05)>=0, 10+ ROUNDDOWN((N4-0.05)*100,0)*1,10 + ROUNDUP((N4-0.05)*100,0)*1)

**(N4=Salesman RAL Rate)**

The narrative version of my calculation is the following.

"*Salesmen earn 10 points for maintaining a RAL rate equal to 5% and gain/lose 1 point for every full percentage over/unde 5%." Example: A RAL rate of 4% would earn 11 points.

However, it is working in reverse order and subtracting 1 point for each percentage under and adding for each percentage under. Any advice would be appreciated.

Thanks! Jim

They should stay at 10 points until they reach a full percentage point below/above. So if they are at 4.5% they would remain at 10 points until they reach 4% and then they would get 11 points. I hope that helps. Thanks!

The logical test is returning as FALSE.

The following works to calculate another portion of my sales competition. However, it does not correctly calculate the points if they are less than the benchmark of 98%. For instance, if they are 97.4% it subtracts six points when it should wait until 97% is reached and then subtract six.

=IF((AN4-0.98)>=0, 10+ ROUNDDOWN((AN4-0.98)*100,0)*6,10 + ROUNDUP((AN4-0.98)*100,0)*6)

Like I said, it works in the other direction. If they reach 100% it yields a total of 22 points. Ten for reaching 98% and an additional 12 for each percentage over.

The logical test for the orginal question is the same except it is looking to see if it is 5% instead of 98%. What is the difference.

I'm sorry, I was trying to explain the logic test from the other spreadsheet was not returning false like this one.

FY11
POINTS
4.26% 9
0.00% 5
0.00% 5
4.00% 9
6.06% 11
4.17% 9
4.88% 9
3.70% 8

=IF((N4-0.05)>=0, 10+ ROUNDDOWN((N4-0.05)*100,0)*1,10 + ROUNDUP((N4-0.05)*100,0)*1)

This is the formula and it is calculating backwards.

Originally Posted by shg

Did you try my formula?

I have the following and it returns false.
=IF((N4-0.05)>=0,10+FLOOR(5-100*N4,SIGN(5-100*N4)))

I used the following and it fixed it.

=IF((N4-0.05)<>0, 10- ROUNDDOWN((N4-0.05)*100,0)*1,10 + ROUNDUP((N4-0.05)*100,0)*1)

Thanks for your help. Jim

........................

Originally Posted by shg

There is no logical test in that formula