< Go Back

Assign points based on late time

Assign points based on late time

Sometimes it so happens that we need to add or assign penalty points based on the amount of time lately and the Excel worksheet contains functions which can be used to do so. In excel we make use of the nested IF function along with the VALUE function to assign points based on late time.

Generic Formula

The generic formula is as shown below

=IF(time <VALUE(“late_time 0”),penalty_point 0,

=IF(time <VALUE(“late_time 1”),penalty_point 1,

=IF(time <VALUE(“late_time 2”),penalty_point 2,

=IF(time <VALUE(“late_time 3”),penalty_point 3,

=IF(time <VALUE(“late_time 4”),penalty_point 4, 5)))))

How does the formula work?

  1. To add or to assign the penalty points based on the late time we can use a nested IF formula as shown.
  2. The formula first checks the late time to check whether the late time is less than 5 minutes. If so then a zero penalty point is assigned to it. For example ‘IF(D5<VALUE(“0:05”),0, ‘ .
  3. The nested IF function tests the values (threshold) in ascending order, and if the result of the above logical test is a FALSE then check for the next threshold say 15 minutes. Repeat the same pattern, as we know that check tests are run in an order from the smallest to the largest because of which there is no need for bracketing which is complex.
  4. The VALUE function treats the threshold times values as a number than treating it as a NEXT.

Example

Let us use this generic formula as shown below to assign points based on late time

=IF(time<VALUE(“0:05”),0,

=IF(time<VALUE(“0:15”),1,

=IF(time<VALUE(“0:30”),2,

=IF(time<VALUE(“0:45”),3,

=IF(time<VALUE(“0:60”),4,5)))))

The table with start time, actual time and late time, actual time and late time is as shown below.

Figure 1. The penalty points for the specified late times are as shown   

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar