# Excel homework | Computer Science homework help

**USING MICROSOFT EXCEL 2016 Independent Project 6-5 (Mac 2016) **

Independent Project 6-5 (Mac 2016 Version)

Classic Gardens and Landscapes counts responses to mail promotions to determine effectiveness. You use SUMIFS and a nested IF formula to complete the summary. You also calculate insurance statistics and convert birth dates from text to dates*. *

*Skills Covered in This Project *

- Nest MATCH and INDEX functions.

- Create DSUM formulas.

- Build an IFS function.

• Build SUMIFS formulas.

• Use DATEVALUE to convert text to

dates.

**Step 1 **

Download start file

- Open the
start file. Click the**ClassicGardens-06****Enable Editing**button. 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.

- Create a nested
*INDEX*and*MATCH*function to display the number of responses from a city.

- Click the
**Mailings**sheet tab and select and name cells**A3:D28**as**Responses**

- Click the
**Mailing Stats**sheet tab.

- Click cell
**B21**and type**Carthage**.

- Click cell
**C21**, start an**INDEX**function, and select the first argument list option.

- Choose the
**Responses**range for the*Array*argument.

- Click the
**Row_num**box and nest a**MATCH**function. Select cell**B21**for the Lookup_value and

cells**A3:A28**on the**Mailings**sheet for the Lookup_array. Click the**Match_type**argument box

and type**0**.

- Click
**INDEX**in the*Formula*bar. Click the**Column_num**box and nest a second**MATCH**function to

look up cell**D3**on the**Mailings**sheet in the lookup array**A3:D3**.

- Click the
**Match_type**box and type**0**(Figure 6-105).**Important:**There is a known bug in*Excel for Mac*that places plus signs (**+**) instead of commas (**,**) between the arguments when using the*Formula Builder*. If this is the case in your*Excel for Mac*version, replace the plus signs with commas.

- Click the

**Excel 2016 **Chapter 6 Exploring the Function Library Last Updated: 3/27/19 **Page 1 **

**USING MICROSOFT EXCEL 2016 Independent Project 6-5 (Mac 2016) **

- Format the results to show zero decimal places.

- Type
**Smyrna**in cell**B21**.

- Use
*DSUM*to summarize mailing data.

- On the
**Mailings**sheet, note that number sent is located in the third column and response data is in

the fourth column.

- Click the
**Criteria**sheet tab. Select cell**B2**and type**lan***to select data for the Landscape Design

department.

- Click the
**Mailing Stats**sheet tab and select cell**B7**.

- Use
**DSUM**with the range name**Responses**as the Database argument. Type**3**for the*Field*

argument, and use an absolute reference to cells**B1:B2**on the**Criteria**sheet as the*Criteria*

argument.

- Copy the formula to cell
**C7**and edit the*Field*argument to use the fourth column.

- Complete criteria for the two remaining departments on the
**Criteria**sheet.

- Click the
**Mailing Stats**sheet tab and select cell**B8**.

- Use
*DSUM*in cells**B8:C9**to calculate results for the two departments.

- On the
- Use
*SUM*in cells**B10:C10**.

- Format all values as
**Comma Style**with no decimal places.

- Create an
*IFS*function to display a response rating.**IMPORTANT:**If you are using a version of**Excel**that does not include the**IFS**function, create a formula using nested**IF**functions instead where each Value_if_false argument is the next IF statement. The innermost nested IF statement should have a Logical_test argument of C7/B7<10%, Value_if_true argument of $C$18, and Value_if_false argument of 0.

- Click cell
**D7**. The response rate and ratings are shown in rows 14:18.

- Start an
*IFS*function and select**C7**for the*Logical_test1*argument. Type**/**for division and select

cell**B7**. Type**>= 20%**to complete the test.

- Click the
**Value_if_true1**box, select**C15**, and press**F4 (FN+F4)**(Figure 6-106).

- Click cell

**Excel 2016 **Chapter 6 Exploring the Function Library Last Updated: 3/27/19 **Page 2 **

**USING MICROSOFT EXCEL 2016 Independent Project 6-5 (Mac 2016) **

- Click the
**Logical_test2**box, select**C7**, type**/**, select cell**B7**, and type**>=15%**

- Click the
**Value_if_true2**box, click cell**C16**, and press**F4**(FN+F4).

- Complete the third and fourth logical tests and
*value_if_true*arguments (Figure 6-107).

- Copy the formula in cell
**D7**to

cells**D8:D10**.

- Use
*SUMIFS*to total insurance

claims and dependents by city and department.

- Click the
**Employee Insurance**

sheet tab and select cell**E25**.

- Use
*SUMIFS*with an absolute

reference to cells**F4:F23**as the*Sum_range*argument.

- The
*Criteria_range1*argument

is an absolute reference to cells**E4:E23**with Criteria1 that will select the city of Brentwood.

- The
*Criteria_range2*argument

is an absolute reference to the department column with criteria that will select the Landscape Design department.

- Complete SUMIFS formulas for cells
**E26:E28**.

- Format borders to remove inconsistencies, if any, and adjust column widths to display data.

- Click the
- Use
*DATEVALUE*to convert text data to dates.

- Click the
**Birth Dates**sheet tab and select cell**D4**. The dates were imported as text and cannot be used in date arithmetic.

- Select cells
**D4:D23**and cut/paste them to cells**G4:G23**.

- Select cell
**H4**and use*DATEVALUE*to convert the date in cell**G4**to a serial number.

- Copy the formula to cells
**H5:H23**.

- Select cells
**H4:H23**and copy them to the*Clipboard*.

- Select cell
**D4**, click the arrow with the**Paste**button [*Home*tab,*Clipboard*group], and choose**Values**(Figure 6-108).

- Format the values in column
**D**

to use the**Short Date**format**.**

- Hide columns
**G:H**.

- Apply
**All Borders**to the data

and make columns**B:D**each**13.57**wide.**NOTE:**Some versions of Excel 2016 for Mac use inches for row height and column width. When viewing the column width, if double quotes appear when displaying the value, enter**1.17”**instead of**13.57**.

- Click the

**Excel 2016 **Chapter 6 Exploring the Function Library

Last Updated: 3/27/19 **Page 3 **

**USING MICROSOFT EXCEL 2016 **

**Independent Project 6-5 (Mac 2016) **

**Step 2 **

Upload & Save

**Step 3 **

Grade my Project

9. Save and close the workbook (Figure 6-109). 10. Upload and save your project file.

11. Submit project for grading.

**Excel 2016 **Chapter 6 Exploring the Function Library