Order from us for quality, customized work in due time of your choice.
Mark the steps as checked when you complete them.
Open the WearEver-06 start fil
Mark the steps as checked when you complete them.
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.
Calculate the number of shipment days.
Select the Shipment Times worksheet tab and select cell D5. The dates in this sheet are volatile because they are determined using the TODAY function in cell B5.
Type = and select cell C5.
Type a minus sign (−), select cell B5, and press Enter. The first shipment required four days.
Use the TEXT function to display the day of the week.
Select cell E5.
Click the Text button [Formulas tab, Function Library group] and choose TEXT.
Select cell C5 for the Value argument.
Click the Format_text argument box.
Type dddd to spell out the day name (Figure 6-95).
TEXT arguments
Figure 6-95 MAC TEXT arguments
Click Done. The arrival day is volatile and your results depend on today’s date in cell B5.
Select cells D5:E5 and double-clilck the Fill pointer.
Evaluate formulas with error triangles.
Close the Formula Builder. Select cell C10.
Click the Error Checking button [Formulas tab, Formula Auditing group]. The error is inconsistent with others in the column.
Click the Evaluate Formula button [Formulas tab, Formula Auditing group] (Figure 6-96). MAC USERS: Skip this step and move on to step 4.h.
Evaluate Formula dialog box
Figure 6-96 Evaluate Formula dialog box
Click Evaluate in the dialog box. The date in cell B10 evaluates to its serial number.
Click Evaluate again. The result is the date in cell C10; this is not an error.
Click Close.
Select cell C10 and click its Trace Error button.
Click the Ignore Error button in the Error Checking dialog box.
Ignore the errors in cells C12, C13, C28, and C35. Click OK in the Alert dialog box.
Nest XMATCH and INDEX to display quantity.
Click the Inventory sheet tab and select cells A3:H50.
Name the range Inventory.
Select cell K5 and type WE006.
Select cell K6, click the Lookup & Reference button [Formulas tab, Function Library group] and choose INDEX.
Select the first argument list array, row_num, column_num and click OK.
Select the Inventory range for the Array argument. Type a comma (,) in the formula bar after the Array argument.
Click the Row_num box, click the Lookup & Reference button, and select XMATCH in the list.
Click inside the XMATCH formula in the formula bar, and click cell K5 for the Lookup_value argument.
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.
Click INDEX in the Formula bar.
Add a comma (,) after the XMATCH formula in the formula bar to get to the Column_num argument. Click the Column_num argument in the Formula Builder.
Click the Lookup & Reference button again and choose XMATCH.
Type quantity in the Lookup_value box.
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.
Click after the parenthesis in the Formula bar to see the completed formula (Figure 6-97).
XMATCH nested twice
Figure 6-97 MAC XMATCH nested twice
Press Enter. The result is 2.
Click cell K5, type WE040, and press Enter. The quantity is updated.
Use SUMIFS to calculate product in stock.
Select cell L13.
Click the Math & Trig button [Formulas tab, Function Library group] and choose SUMIFS. You can also find SUMIFS in the function list in the Formula Builder if it is still open.
Select cells E4:E50 for the Sum_range argument and make the references absolute.
Click the + button to add an argument. Click the Criteria_range1 box, select cells C4:C50, the “Color” field, and make the references absolute.
Click the + button. Click the Criteria1 box and select cell K13. Leave this as a relative reference.
Click the + button again. Click the Criteria_range2 box, select cells B4:B50, and make the references absolute.
Click the + button again to complete the formula. Click the Criteria2 box and select cell J13 (Figure 6-98).
SUMIFS to calculate quantity by product and color
Figure 6-98 MAC SUMIFS to calculate quantity by product and color
Click Done. The result is 16.
Copy the formula in cell L13 to cells L14.
Sort data and use SUMIFS to identify blank cells.
Select cells A4:H50 and click the Sort button [Data tab, Sort & Filter group].
Sort the data in ascending order by Product. Results in columns K and L are not affected.
Select cell L16.
Click the Recently Used button [Formulas tab, Function Library group] and choose SUMIFS.
Select cells E4:E50 for the Sum_range argument. This formula will not be copied and absolute references are not necessary.
Click the + button to add an argument. Click the Criteria_range1 box and select cells D4:D50, the “Size” field. Handbag products do not have a size.
Click the + button. Click the Criteria1 box and type = (an equals sign). An equals sign as criteria finds blank cells.
Click the + button again. Click the Criteria_range2 box, select cells F4:F50, the gender. Handbags do not display a gender.
Click the + button again to complete the formula. Click the Criteria2 box and type = (an equals sign) (Figure 6-99).
SUMIFS to calculate blank cells
Figure 6-99 MAC SUMIFS to calculate blank cells
Click Done. The result is 26.
Prepare the criteria sheet for a Database function.
Click the Satisfaction Survey worksheet tab and review the data.
Select cells A4:G40 and name the range as Survey.
Click the Criteria sheet tab.
Select cell A2 and type rug*, criteria for the Rugged Hiking Boots.
Select cell A5. Type the criteria as shown in Table 6-6 for the shoe styles.
Table 6-6:
Cell Criteria
A5 com*
A8 laz*
A11 ser*
A14 gli*
Use DAVERAGE to summarize customer survey data.
Click the Average Ratings worksheet tab and select cell B5.
Click the Insert Function button [Formulas tab, Function Library group].
Scroll through the list of functions to find the Database category.
Select DAVERAGE and click Insert Function in the Formula Builder to calculate an average comfort rating for the boots.
Choose Survey for the Database argument.
Click the Field box, select cell B4, and create a mixed reference B$4.
Click the Criteria box, select the Criteria sheet tab, select cells A1:A2, and make the references absolute (Figure 6-100).
DAVERAGE for comfort rating
Figure 6-100 MAC DAVERAGE for comfort rating
Click Done. The result is 7.75.
Copy the formula in cell B5 to cells C5:D5.
Copy and edit formulas.
Select cell B5 and copy the formula to cells B6:B9.
Select cell B6. Click the Insert Function button if necessary to view the copied formula. The Formula Builder displays a #VALUE! error for the criteria.
Click the Criteria box and edit the argument to display $A$4:$A$5 for the Comfy Walking Shoes.
Click Done. The result is 7.5.
Close the Formula Builder, select cell B6 and click its Trace Error button. The formula is different from the one above.
Choose Ignore Error.
Select cell B7 and edit the Criteria argument to show $A$7:$A$8 for the Lazy Flip Flops. You can edit the formula in the Formula bar or the Function Arguments dialog box.
Edit the Criteria arguments in cells B8 and B9 to use the correct criteria for each shoe style.
Select cells B5:B9 and copy them to reach column D.
Select cells E5:E9, click the AutoSum arrow [Home tab, Editing group], and choose Average.
Create an IFS function.
Select cell F5, click the Logical button [Formulas tab, Function Library group], and choose IFS.
Click the Logical_test1 argument, select cell E5, and type >=9.
Click the + button. Click the Value_if_true1 box, click cell H5, and make the reference absolute.
Click the + button. Click the Logical_test2 box, click cell E5, and type >=8.
Click the + buton again. Click the Value_if_true2 box, click cell H6, and make the references absolute.
Click the + button. Click the Logical_test3 box, click cell E5, and type >=5.
Click the + button again to reveal the Value_if_true3 box, click cell H7, and make the references absolute.
Click the + button again to reveal the Logical_test4 box, click cell E5, and type <5.
Click the + button again. Click the Value_if_true4 box, click cell H8, and press make references absolute (Figure 6-101).
IFS function with four logical tests
Figure 6-101 MAC IFS function with four logical tests
Click Done.
Copy the formula to complete the column.
AutoFit column F.
Order from us for quality, customized work in due time of your choice.