Use the power of logic in spreadsheets
Say you’re trying to use an Excel spreadsheet to track weekly wages for office employees. It’s simple enough to create a formula (hours x hourly rate) that calculates wages for someone who works 40 hours or less. But what if someone works 45 hours one week?
That’s when you need the IF function in Excel, says Annette Marquis of Triad Consulting. Use the IF function when you want Excel to apply different formulas to a cell, based on what content you type in that cell.
In the example above, you need a formula that can calculate wages for the first 40 hours, then calculate time-and-a-half for the other five hours.
Other ways to use the IF function: figuring out commission rates that vary based on total volume, or allocating vacation days based on seniority.
Here’s how to set it up, using the first example:
1. Start an IF function in cell E5. From the top bar menu, select Insert > Function > IF.
2. You’ll see a window pop up with three fields: “logical test,” “value if true” and “value if false.”
3. In the “logical test” field, tell Excel what you want it to look at. In this case, you want it to determine if the hours worked (column C) are over 40. Type “C5>40.”
4. In the “value if true” field, tell Excel what to do if the hours are over 40: Calculate the first 40 hours at the regular rate. Subtract 40 from the total number of hours worked. Then multiply the remaining number by time-and-a-half (1.5 times rate).
That formula would look like this: “40*D5+(C5-40)*D5*1.5.”
5. In the “value if false” field, tell Excel what to do if the hours are not over 40: Use the standard hours-times-rate formula. That formula would look like this: “D5*C5.”
6. Click OK to close the window.
Now, you have an easy way to calculate overtime … and you don’t even have to find your calculator!