There are times when we need to assign penalty points on late time. To do this, we can utilize a nested IF formula. This post provides a clear guide on how to use this function to assign points on late time in excel.
Figure 1: How to assign points on late time
General syntax of the formula
=IF(time<VALUE(“0:05”),0,IF(time<VALUE(“0:15”),1,IF(time)<VALUE(“0:30”),2,IF(time)<VALUE(“0:60”),3,IF(time)<VALUE(“6:00”),4,5)))))
Where;
Time- is the “late by” time
How the formula works
This formula tests the threshold values in an ascending order. The formula first checks the “late by” column, which is column D2:D10 in the above example, before returning an answer for the late points.
In our case above, the formula for cell E2 is
=IF(D2<VALUE(“0:05”),0,
- If the condition is satisfied, that is if it is less than 0:05, then it will assign it zero points.
- In the event that the logical test turns out to be FALSE, the formula checks D2 is less than the next threshold, which is 0:15, then it will assign it 1 points.
- This pattern keeps repeating itself for each threshold.
- We use the VALUE function in the formula to enable it treat the threshold as a number and not as text.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment