Remember that the general format of IF is IF(condition,x,y)
In Exercises 6-12 and 6-13 you worked with IF with only one condition: if the condition were true, then the value of x was displayed in the cell; otherwise, y was displayed. In business applications, many decisions require more than one condition. Choices must be made between several alternatives. You can deal with such situations by using a programming concept called
nesting. The idea of nesting is that you place one IF inside another so as to create a two-step
test; the principle can be extended to make multiple-nested conditions.
One way to nest IF is to enter an additional IF as argument y. This practice is considered good worksheet style because it makes the logic of the complete formula easier to read.
Suppose that instead of using the same markup for store 2 and store 3, management decided that the markup should be 18% for store 2 and 16% for store 3.
The solution is to build a nested IF for cell G3. Before you use it in the next exercise, study the following formula:
IF(E3=1,D3*1.14,IF(E3=2,D3*1.18,D3*1.16)) This complete IF function means:
“If E3 equals 1, then display the value D3 multiplied by 1.14;
otherwise, if E3 equals 2, then display the value D3 multiplied by 1.18; otherwise, display the value D3 multiplied by 1.16.”
Notice the built-in assumption that if the store number is not 1 or 2, then it must be 3. Clearly, you would have to modify the formula again if XYZ Sales Limited added a fourth store.
Note:
When using nested formulas with a large number of parentheses, always verify that the parentheses have been entered correctly, that is, in pairs. For example, in the formula just described, the number of left (opening) parentheses must equal the number of right (closing) parentheses. When you type any Excel function that includes parentheses, verify that this balance is correct before you press ENTER and place the formula in the cell. If the balance is incorrect, Excel will try to balance the closing parentheses by adding one to the end. If this does not work, Excel will place you in EDIT mode and wait for you to correct the error before placing the completed formula in the cell.
You can have up to 64 nested IF functions within an IF function. EXERCISE 6-14
Using IF with multiple conditions
In this exercise, you will construct formulas using the nested IF function to take into account the different markups for stores 1, 2, and 3.
1. Begin the exercise using the same workbook as Exercise 6-13 (or open the workbook CT2L6P6S.XLS).
2. Click cell G3. Edit the formula in cell G3 to read:
=IF(E3=1,D3*1.14,IF(E3=2,D3*1.18,D3*1.16))
After entering this formula, the value in cell G3 should change to 4,105.51 (that is, 3,539.23 multiplied by 1.16).
3. Similar to the procedure in Exercise 6-13, use Fill and choose Down to copy the formula in cell G3 to G4:G16. The result in cell G16 should be 3,733.83. Remember to redraw the underline for cell G16.
4. If you want to review or use this workbook in the future, save it under your own initials, then close the file. If you do not obtain the results indicated, you can open the workbook CT2L6P7S.XLS and study the formulas in column G.
TOPIC 6.10
Using Excel Help
Excel Help enables you to review some of the techniques learned in this lesson. Use the following steps to display the appropriate Help windows:
1. Click on the Help icon.
2. Type formulas, entering and click Search.
3. Select Create or delete a formula.
4. After reading the Help information, search other topics of interest to you.
5. If you had difficulty with any of the following topics, display the corresponding Help information to review:
• Cell references • Circular references
• Change the way Microsoft Excel calculates formulas • How formulas calculate values
• About using functions to calculate values • About logical functions
TOPIC 6.11
Self-testing questions
1. State the Excel formula to add 149 to 34 and multiply the result by 165.
2. Without using Excel, calculate the results of each of the following, given the contents of the cells. Then enter the values and formulas in an Excel worksheet. Compare the results to your manual calculations.
Cells Contents Cells Contents
B3 1,200 C5 5 B4 200 C6 4 B5 10 C7 2 a. =B3–B4/C5 b. =(B3–B4)/C5 c. =B3/B5–C5*C6 d. =B3/(B5–C5)*C6 e. =(B3/B4)^C7
3. Without using Excel, calculate the results of each of the following, given the contents of the cells. Then enter the values and formulas in an Excel worksheet. Compare the results to your manual calculations.
Cells Contents Cells Contents B6 100 D6 150 B7 200 D7 210 B8 10 D8 15 B9 60 D9 60 a. =(B6+50)=D6 b. =B7>D7 c. =B8>=D8 d. =B9<D9
4. You are building a worksheet to keep track of the inventory costs for 1,000 inventory items in five warehouses. Row 3 is the first row of the inventory list. In cell A3, enter the inventory name of the first item, and in cell B3, the item’s unit cost. Cell C3 contains the inventory count of the items in warehouse 1. Cells D3, E3, F3, and G3 contain counts of the item in warehouses 2 to 5.
You wish to enter the formulas in the range H3:L3 to compute the costs of the inventory in each warehouse, starting with H3 for warehouse 1.
a. What should be the formula in cell H3 so that you can copy the cell formula to the range I3:L3?
Hint:
You may need to use an absolute or mixed cell reference.
b. Can the formulas in the range H3:L3 be copied to rows 4 to 1003 to complete the inventory worksheet for the entire 1,000 items without any change from you? If so, why? If not, explain.
5. You have created an Excel worksheet that included a column of calculated values. However, the column total does not match the sum of the individual numbers in the column. Briefly describe two methods to make the sum of the individual numbers match that of the column total.
6. a. Are circular references always errors?
b. If you suspect a formula contains a circular reference, how do you go about finding which cells are involved in the circular reference?
7. a. Explain the meaning of a nested IF function. How many levels of nesting is allowed by Excel?
b. Which of the following formulas contain(s) a nested IF function:
Formula A: =IF(C8=D8,SUM(C4:C7),AVG(C4:C7)*G9+1000) Formula B: =IF(F9=12,500,IF(G9>100,1000,1500))
LESSON 7
Charts
Topic outline
7.1 Basic concepts
7.2 Creating and modifying a chart 7.3 Two- and three-dimensional charts 7.4 Formatting a chart
7.5 Creating a chart sheet 7.6 Printing charts 7.7 Types of charts
7.8 Designing charts in good style 7.9 Using Excel Help 7.10 Self-testing questions
Overview
A chart is a graphic representation of information. Excel provides two easy methods to create charts from worksheet data: as an object embedded on a worksheet, or as a separate chart sheet, which can be useful if you want to show the chart on its own.
In this lesson, you will create charts by each method. You will study the various types of charts that you can create in Excel. You will also learn to design charts in good style and to print charts, and obtain Excel Help on creating charts.
Learning objectives
• Describe the use of charts in Excel. • Create charts.
• Add and modify chart items. • Format chart items.
• Create chart sheets. • Print charts.
• Describe chart types available in Excel.
• Use guidelines for designing charts in good style. • Obtain Excel Help on charts.