Use the power of logic in spreadsheets — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily

Use the power of logic in spreadsheets

by on
in Excel Training,Leaders & Managers,Management Training,Office Technology

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.

This Executive Summary packs two training webinars into one compact report of 28 pages, complete with screen captures and Q&A from the events. Get it now as a PDF download or printed copy.

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."

For a concise Executive Summary, Microsoft Excel: Time-Savers for Every Skill Level packs a wallop. Soon you'll be squeezing every ounce of productivity out of Excel. Get it here.

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!

Some people moan and groan about Microsoft's dominance in office software. Not us. We think Excel is a terrific product for tracking and manipulating data – if you know how to use the software.

That's why we teamed up with guru Melissa Esquibel to offer you this print version of her training.
book cover
Then we asked ourselves, how could we make this report even more valuable? By adding the questions asked by participants in the webinars, plus Melissa's helpful answers. Result: an appendix that answers questions you may be wrestling with.

It's all about saving time, the one thing you can't buy more of.

Get your copy now!

Leave a Comment