Using MAX and MIN instead of IF function
I must calculate the time-and-a half and double-time hours from a total overtime figure – in column C. The first two hours of overtime are time-and-a-half, the rest is double-time. Can you provide IF functions to do the calculations?
By Neale Blackwood
Many of the questions I receive are based on IF functions and how to use them correctly. Assuming the first row used is Row 2, the formula for time-and-a-half would be
The formula for double-time would be
=IF(C2>2,C2-2,0). In the above case it is possible to calculate both overtimes without using an IF function. The time-and-a-half alternative formula is =MIN(C2,2) and the double-time formula is =MAX(C2-2,0). In both of the above solutions you could also calculate double-time by subtracting the time-and-a-half amount from the total overtime amount in column C.
You can use the MIN and MAX functions to perform some basic logic calculations without using an IF Function. There are times when you need to use only the positive numbers in a list and zero the negatives. Assuming cell A1 contains a number the following formula will display a positive number from A1, but will display 0 if A1 has a negative. =MAX(0,A1).