create a formula to explain driver violation scores

[DRIVERMENTORIAT]

I have the want to create a formula to make my DRIVER SCORECARD easier to manage. I would like to take this information, and learn how to create a formula that can be used in Excel 2010. Any help would be greatly appreciated.
Here is my information:
Driver Log Violations- Threshold or maximum to receive top score- 2
- Score for below 2- 20
I need a formula to calculate what percentage or average would 3 violations or 4 violations or 22 violations would arise.
For instance-
Driver 1 - 2 violations, 20 points
Driver 2 - 20 violations, ______________
This is the information that I am struggling with. Can anyone help?
Thanks!
Michael Barfoot
Driver Mentor

[Melvinrobb]

You're going to need explain how the point system is calculated a bit better.
Creating a mock workbook showing different scenarios (ex. 2 violations, 10 violations, etc..) and manually showing what you would want the formula to return.

[DRIVERMENTORIAT]

Here is what I am trying to accomplish:
If 2 violations and below are 20 points, and 20 violations and above are 0 points, what is the point value of 14 violations? or 8 violations?
Please excuse my ignorance on the issues. I am dead new at this and I have been handed this information and asked to figure it out. Unfortunately going to school to learn this is not an option for me at this time. I have a general understanding, but this stuff boggles my brain!
Does this help Melvinrobb?

[Melvinrobb]

So 20 points = 18 violations in a way.

20/18 = 1.111111
This represents how many points should be subtracted for each violation. Here is a formula that will work:

=IF(A1>2,MAX(0,20-(A1-2)*20/18),20)

[DRIVERMENTORIAT]

THANK YOU SO MUCH Melvinrobb. Now I have one more question. Can you explain how this works so I may understand the sequencing? I know I am being a pain and I appreciate this.

[Melvinrobb]

The If statement makes the formula return 20 if the # of violations is not >2
The max formula exists so that the score won't become negative (the score will always be atleast 0).
A1-2 give us the # of violations that need to be applied to the point value. so... for example. if the number of violations is 20

20-2=18 (the first two violations don't apply, so they are taken off)

18*20/18 = 20 (20/18=1.1111 this is how many points are subtracted for each additional violation)

20-20 = 0 (you start with 20 points, and get 20 points subtracted, based on the number of violations.)

So you get a score of 0 for this individual.

[DRIVERMENTORIAT]

Thank you Melvinrobb for your valuable information!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.