INT Function Usage

[james.kilgore]

I'm currently using the following function to calculate the below:
=IF(T4>=S4,20+INT((T4/S4-1)*100)*5,0)

Salesmen will earn 20 points when they EAD 100% of their PJ/CCT/SOWT target. Salesmen will then earn an additional 5 points for every PJ/CCT/SOWT to EAD above 100% of their target. Flights will earn 0 points if they fail to meet 100% of PJ/CCT/SOWT target.

However, it is calculating the percentage over 100%. I need it to give 20 points for reaching the target and then give 5 points for each one above the set target.

For instance, a salesman has a goal of 10 and gets 12. They would get 20 points for reaching the target and then receive 10 more points for the extra two for a total of 30 points.

DESCRIPTION OF PROBLEM: it is calculating the points based on the percentage. What I need it to do is evaluate column S compare to column R. If S is equal to the target then award 20 points. For every integer over the target award 5 additional points.

Thanks!

 

[capparfrank]

You are using incorrect logic, given the example you provide. Try this:

=IF(actual >= goal, 20 + 5 * (actual - goal), 0)

If the salesperson achieves their target they get 20 plus 5 times however much they may have exceeded their target by. Otherwise, they get zero.

There is no reason to calculate a percentage here.

 

[Scott Huish]

That could be shortened to:

=MAX(20 + 5 * (actual - goal), 0)

 

[capparfrank]

Hotpepper, I don't think that is quite correct given the requirements.

Flights will earn 0 points if they fail to meet 100% of PJ/CCT/SOWT target.

Your formula would return 15 points given a goal of 10 and an actual of 9. However, since salesperson did not reach its goal, it should receive 0 points.

 

[Scott Huish]

You're right. Sorry.