Excel 2016 Tips and Tricks

Excel 2016

Lesson 3 – Using Advanced Functions

E XERCISE

U SING A DVANCED F UNCTIONS



T ASK Use advanced functions in a workbook.

1. Open FUNCTEX.XLSX . 2. Display the Hours worksheet, if necessary.

3. Select cell F6 and use the VLOOKUP function to calculate the pay rate for full-time, part- time, or overtime work, according to job type. ( Hint: The lookup value is the Job Type in C6, the lookup table is named table, and the column index number is the Type of Time in E6. )

4. Copy the formula to the range F7:F12. 5. Display the Sales-Previous worksheet.

6. Customers get free shipping if their average sales are more than 20,000. In cell I5, use an IF function to test the average sales. If the average sales (H5) are greater than or equal to 20,000 , Yes should appear in the cell; otherwise, No should appear in the cell.

( Hint: Do not include a comma in 20000.) 7. Copy the formula to the range I6:I13.

8. The customer discount depends on the customer status. In cell J5, use a nested IF function to determine the correct discount for each customer according to the following table. ( Hint: Test to see if the status in cell B5 is a 1 , 2 , or 3 ; otherwise, a discount of 10% is entered. Enter 5% as 0.05 . )

Status

Discount

1

0%

2

5%

3

7.5%

4

10%

9. Copy the formula to the range J6:J13. 10. A customer status of 2 or 4 indicates a long-standing, good credit history and the customer credit limit should be raised by 5%; all other credit limits will remain the same. In cell K5, use an OR condition in an IF function to display which customers should have their credit limits raised. Display 5% or 0% in the cells accordingly. 11. Copy the formula in cell K5 to K6:K13. 12. Display the Sales-Current worksheet. 13. In cell C16, use an ISERROR function in an IF function to change the #DIV/0! message to No data . Copy the formula to the range D16:F16. 14. Display the Commissions worksheet.

OFFICEPRO, Inc.

Page 95