Excel 2024 In Practice - Ch 6 Guided Project 6-3
SIMnet Excel project instructions
· Excel Projects Help
Guided Project 6-3 - SIMnet Project Steps to complete This Project
Mark the steps as checked when you complete them.
1.Open the WearEver-06 start file. If the workbook opens in Protected View, click the Enable Editing button so you can modify it. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
Evaluate a cell with an error triangle.
2.Select the Shipment Times worksheet tab. AutoFit column C to display all data if necessary. Columns that are not wide enough display a series of # symbols.
3.Select cell C10 and click the Trace Error button. The formula is inconsistent with others in the column.
4.Click the Evaluate Formula button [Formulas tab, Formula Auditing group]. Cell B10 is the first step for evaluation.
5.Click Step In in the dialog box to evaluate the expression in cell B10 which is TODAY() (Figure 6-105).
A screenshot shows the Evaluate Formula dialog box.The Evaluate Formula dialog box is open. It shows two panes: Reference on the left and Evaluation in line with it on the right, separated by an equal sign. The Reference pane shows the text 'Shipment Times' exclamation $C $10 followed by 'Shipment Times' exclamation mark $B $10 (highlighted in blue). On the right, the Evaluation feature is set to B 10 (highlighted in blue) plus 7 and TODAY () (underlined), respectively. A text at the bottom of this pane reads: A function in this formula causes the result to change each time the spreadsheet is calculated. The final evaluation step will match the result in the cell, but interim steps may not. The Evaluate, Step In, Step Out, and Close buttons are at the bottom of the dialog box. The Evaluate button is selected.
Figure 6-105 Evaluate Formula dialog box
6.Click Step Out to return to the formula in cell C10. The serial number for TODAY() displays.
7.Click Evaluate. The formula calculates.
8.Click Close. There are no errors in the Arrived column; the formulas to calculate the arrival date are purposely varied.
9.Select cell C10, click its Trace Error button, and select Ignore Error.
Ignore the errors in cells C12, C28, and C35.
Calculate the number of shipment days.
10.Select cell D5, type = and select cell C5.
11.Type a minus sign (−), select cell B5, and press Enter. The first shipment required four days.
12.Use the TEXT function to display the day of the week.
13.Select cell E5, click the Text button [Formulas tab, Function Library group], and choose TEXT.
14.Select cell C5 for the Value argument.
15.Click the Format_text argument box and type dddd to spell out the day name.
16.Click OK. Your results depend on today’s date in cell B5. If error triangles re-appear, you need not clear them.
17.Select cells D5:E5 and double-click the Fill pointer.
Nest XMATCH and INDEX to display quantity.
18.Click the Inventory sheet tab and select cells A3:H50.
Name the range Inventory.
19.Select cell K5 and type WE006.
20.Select cell K6, click the Lookup & Reference button [Formulas tab, Function Library group], and choose INDEX.
21.Select the first argument list array, row_num, column_num and click OK.
22.Press F3 (FN+F3) and select Inventory for the Array argument.
23.Click the Row_num box and click the Name box drop-down arrow. Choose XMATCH in the list or choose More Functions to find and select XMATCH.
24.Click cell K5 for the Lookup_value argument.
25.Click the Lookup_array box and select cells A3:A50. This XMATCH function looks in column A for the row that matches the data in cell K5.
26.Click INDEX in the Formula bar. (Click OK if the argument list opens.)
27.Click the Column_num argument in the INDEX Function Arguments dialog box.
28.Click the Name box drop-down arrow and choose XMATCH.
29.Type quantity in the Lookup_value box.
30.Click the Lookup_array box and select cells A3:H3. This XMATCH function finds the cell in the “Quantity” column for the row identified by the first XMATCH function.
31.Click INDEX in the Formula bar to see the completed formula (Figure 6-106).
A screenshot shows the Function arguments dialog box with X MATCH function nested twice.The Function Arguments dialog box is open, showing a pane for INDEX. The pane shows Array, Row_number, and Column_number features, each showing a collapse button. They are set to Inventory, X MATCH (K 5, A 3 colon A 50), and X MATCH ("quantity", A 3 colon H 3), respectively. On the right of each feature shows the texts: equals {"Product I D", "Product", "Color", "Size", "Women's", "Cost", "Retail"; "W E 001", Ru, and so on, equals 7, and equals 5, respectively. Below this pane shows the Formula result equals 2. A text at the bottom of this pane reads: Returns a value or reference of the cell at the intersection of a particular row and column, in a given range. Column_number (highlighted in bold) selects the column in Array or Reference from which to return a value. If omitted, Row_number is required. The bottom pane shows the text: Formula result equals 2. A blue hyperlink Help on this function is shown below it. The OK and Cancel buttons are at the bottom of the dialog box. The Ok button is selected.
Figure 6-106 XMATCH nested twice
32.Click OK. The result is 2.
33.Click cell K5, type WE040, and press Enter. The quantity is updated.
34.Sort data and use SUMIFS to identify blank cells in column D.
35.Select cells A4:H50 and click the Sort button [Data tab, Sort & Filter group].
36.Sort in ascending order by Product to scramble the empty cell locations in the Size column.
37.Select cell K8, click the Math & Trig button [Formulas tab, Function Library group], and choose SUMIFS.
38.Select cells E4:E50 for the Sum_range argument. This formula will not be copied so absolute references are not necessary.
39.Click the Criteria_range1 box, select cells D4:D50, the “Size” field. Handbag products do not have a size.
40.Click the Criteria1 box and type = (an equals sign). This criteria finds blank cells.
41.Click the Criteria_range2 box, select cells H4:H50. You will count handbags with a retail price greater than $75.
42.Click the Criteria2 box and type >75 (Figure 6-107).
A screenshot shows the Function arguments dialog box with SUM IFS function to identify blank cells.The Function Arguments dialog box is open, showing a pane for SUM IFS. The pane shows Sum_range (highlighted in bold), Criteria_range 1 (highlighted in bold), Criteria 1 (highlighted in bold), Criteria_range 2, and Criteria 2 features, each showing a collapse button. They are set to E 4 colon E 50, D 4 colon D 50, "equals", H 4 colon H 50, and "greater than 75", respectively. On right of each feature shows the texts: equals {4;3; 4; 3; 6; 4; 1; 4; 1; 2; 3; 3; 6; 6; 6; 2; 3; 1; 5; 4; 0; and so on; equals {0; 0; 7; 5; 8; 5; 8; 8; 5; 9; 7; 5; 8; 7; 5; 8; 0; 8; 9; 1 and so on; equals "equals", equals {85; 85; 70; 70; 70; 65; 65; 65; 65; 65; 65; 60; and so on; and equals "greater than 75", respectively. Below this pane shows the Formula result equals 17. A text at the bottom of this pane reads: Adds the cells specified by a given set of conditions or criteria. Criteria 2: (highlighted in bold) is the condition or criteria in the form of a number, expression, or text that defines which cells will be added. The bottom pane shows the text: Formula result equals 17. A blue hyperlink Help on this function is shown below it. The OK and Cancel buttons are at the bottom of the dialog box. The Ok button is selected.
Figure 6-107 SUMIFS to identify blank cells
43.Click OK. There are 17 handbag products that cost more than $75.
44.Use UNIQUE and nest INDEX with RANDARRAY to display a list of product names.
45.Select cell N11. The four products for promotion are randomly decided.
46.Click the Lookup & Reference button [Formulas tab, Function Library group] and choose UNIQUE.
47.Select cells B4:B50 and click OK. The result is a dynamic array with 11 items.
48.Select cell P11, click the Lookup & Reference button [Formulas tab, Function Library group], and choose INDEX.
49.Select the first argument array and click OK.
50.Select cell N11 for the Array argument and type # immediately after the address. N11# identifies the dynamic array that begins in cell N11.
51.Click the Row_num argument box, click the Name box arrow, and choose or find RANDARRAY.
52.Select cell K10 for the Rows argument in the RANDARRAY dialog box.
53.Click the Columns box and type 1.
54.Type 1 for the Min argument and 11 for the Max argument.
Leave the Integer box empty because the result array will be text, not values.
55.Click INDEX in the Formula bar to see the nested formula (Figure 6-108).
A screenshot of an Excel worksheet shows a list of product names and a Function Arguments dialog box with RAND ARRAY nested to display the names.The spreadsheet shows a list titled Product Names on a gridless background as follows: All Around Shoulder Bag, Chunky Heel Boots, Comfy Walking Shoes et cetera. On the right of the list is a formula under the title Group for Next Promotion as equals INDEX (N subscript 11 hash symbol,. The cell with the formula is selected. The Function Arguments dialog box is open, showing a pane for INDEX. The pane shows Array, Row_number, and Column_number features, each showing a collapse button. They are set to N 11 hashtag, RAND ARRAY (K 10, 1, 1, 11), and blank, respectively. On right of each feature shows the texts: equals {"All Around Shoulder Bag"; "Chunky and so on; equals Volatile, and number (grayed), respectively. Below this pane shows the Formula result equals Volatile. A text at the bottom of this pane reads: Returns a value reference of the cell at the intersection of a particular row and column, in a given range. Array (highlighted in bold) is a range of cells or an array constant. The bottom pane shows the text: Formula result equals Volatile. A blue hyperlink Help on this function is shown below it. The OK and Cancel buttons are at the bottom of the dialog box.
Figure 6-108 RANDARRAY nested to display names
56.Click OK. The result array is dynamic. It randomly displays four products from the array in cell N11. If a product is duplicated in the list, press F9 (FN+F9) to recalculate.
Prepare the criteria sheet for a Database function.
57.Click the Satisfaction Survey worksheet tab and review the data.
58.Select cells A4:G40 and name the range as Survey.
59.Click the Criteria sheet tab, select cell A2, and type rug*, criteria for the Rugged Hiking Boots.
60.Select cell A5. Type the criteria as shown in Table 6-4 for the shoe styles.
Table 6-4:
Table 6-4: The table lists the criteria to type for the shoe styles.
Cell Criteria
A5 com*
A8 laz*
A11 ser*
A14 gli*
61.Use DAVERAGE to summarize customer survey data.
62.Click the Average Ratings worksheet tab and select cell B5.
63.Click the Insert Function button [Formulas tab, Function Library group].
64.Choose Database in the Or select a category list.
65.Select DAVERAGE and click OK to calculate an average comfort rating for the boots.
66.Press F3 (FN+F3), choose Survey for the Database argument, and click OK.
67.Click the Field box, select cell B4, and press F4 (FN+F4) two times to create a mixed reference B$4.
68.Click the Criteria box, select the Criteria sheet tab, select cells A1:A2, and press F4 (FN+F4) to make the references absolute (Figure 6-109).
A screenshot shows the Function arguments dialog box with D AVERAGE function for comfort rating.The Function Arguments dialog box is open, showing a pane for D AVERAGE. The pane shows Database, Field, and Criteria features, each showing a collapse button. They are set to Survey, B $4, and Criteria exclamation $A $1 colon $A $2, respectively. On the right of each feature shows the texts: equals {"Order I D", "Product", "Color", "Size", and so on; equals "Comfort", and blank, respectively. Below this pane shows the Formula result equals 7.75. A text at the bottom of this pane reads: Averages the values in a column in a list or database that match conditions you specify. Database (highlighted in bold) is the range of cell that makes up the list or database. A database is a list of related data. The bottom pane shows the text: Formula result equals 7.75. A blue hyperlink Help on this function is shown below it. The OK and Cancel buttons are at the bottom of the dialog box. The Ok button is selected.
Figure 6-109 DAVERAGE for comfort rating
69.Click OK. The result is 7.75.
70.Copy the formula in cell B5 to cells C5:D5.
71.Copy and edit formulas.
72.Select cell B5 and copy the formula to cells B6:B9.
73.Select cell B6. Click the Insert Function button [Formulas tab, Function Library group] to open the Function Arguments dialog box for the copied formula. The dialog box displays a #VALUE! error for the criteria.
74.Click the Criteria box, edit the argument to display $A$4:$A$5 for the Comfy Walking Shoes, and click OK. The result is 7.5.
75.Select cell B6 and click its Trace Error button. The formula is different from the one above.
76.Choose Ignore Error.
77.Select cell B7 and edit the Criteria argument for Lazy Flip Flops. You can edit the formula in the Formula bar or the Function Arguments dialog box.
78.Edit the Criteria arguments in cells B8 and B9 to use the correct criteria for each shoe style.
79.Copy cells B6:B9 to reach column D.
80.Select cells E5:E9, click the AutoSum arrow [Home tab, Editing group], and choose Average.
81.Create an IFS function.
82.Select cell F5, click the Logical button [Formulas tab, Function Library group], and choose IFS.
83.Click the Logical_test1 argument, select cell E5, and type >=9.
84.Click the Value_if_true1 box, click cell H5, and press F4 (FN+F4) to make the reference absolute.
85.Click the Logical_test2 box, click cell E5, and type >=8.
86.Click the Value_if_true2 box, click cell H6, and press F4 (FN+F4).
87.Complete the Logical_test3 and Logical_test4 conditions (Figure 6-110 and table below).
A screenshot shows the Function arguments dialog box for IFS function with multiple logical tests.The Function Arguments dialog box is open, showing a pane for IFS. The pane shows Logical_test 2, Value_if_true 2, Logical_test 3, Value_if_true 3, and Logical_test 4 features, each showing a collapse button. They are set to E 5 greater than or equal to 8, $H $6, E 5 greater than or equal to 5, $H $7, and E 5 less than 5, respectively. On the right of each feature shows the texts: equals TRUE, equals "Above Average", equals TRUE, equals "Average", and equals FALSE, respectively. Below this pane shows the Formula result equals "Above Average". A text at the bottom of this pane reads: Checks whether one or more conditions are met and returns a value corresponding to the first TRUE condition. Logical_test 4: (highlighted in bold) is any value or expression that can be evaluated to TRUE or FALSE. The bottom pane shows the text: Formula result equals Above Average. A blue hyperlink Help on this function is shown below it. The OK and Cancel buttons are at the bottom of the dialog box. The Ok button is selected.
Figure 6-110 IFS function with multiple logical tests
Logical_test3 E5>=5
Value_if_true3 $H$7
Logical_test4 E5<5
Value_if_true4 $H$8
88.Copy the formula to complete the column and AutoFit column F.
89.Save and close the workbook. Remember that your date and dynamic array results will not match the figure (Figure 6-111).
A screenshot of two Excel sheets shows tables for Wear Ever Shoes and Accessories with shipment times and product ratings respectively.The first table is titled Wear Ever Shoes and Accessories: Shipment Times and summarizes order numbers, shipped and arrival dates, number of days, and arrival days for multiple shipments. The second table is titled Wear Ever Shoes and Accessories: Average Ratings by Shoe Style and summarizes shoe products with comfort, fit, style, overall score, and rating categories such as Excellent, Above Average, Average, and Poor.
Figure 6-111 Excel 6-3 completed
A screenshot of two Excel sheets shows tables for inventory details, and promotion counts respectively.The first is titled Wear Ever Accessories Inventory: September 30 and summarizes inventory with product I D, product name, color, size, quantity, gender category, cost, and retail price. The second section contains a summary of handbag product counts, items for the next promotion, and grouped product names for upcoming promotions. Alongside this, grouped product names are listed for upcoming promotions.
Figure 6-111 Excel 6-3 completed
90.Upload and save your project file.
91.Submit file for grading.