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.
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?
- To add or to assign the penalty points based on the late time we can use a nested IF formula as shown.
- 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, ‘ .
- 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.
- The VALUE function treats the threshold times values as a number than treating it as a NEXT.
Let us use this generic formula as shown below to assign points based on late time
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