Project excel

Finish excel to satisfactory

Mailings by City and Department
City
Department
# Mailed # Responses
Ashland City
Lawn & Maintenance
1690
210
Belle Meade
Brentwood
Patio & Furniture
Lawn & Maintenance
2170
938
435
125
Burns
Patio & Furniture
1475
413
Carthage
Lawn & Maintenance
1225
343
Centerville
Landscape Design
1260
353
Charlotte
Landscape Design
2235
618
Coopertown
Lawn & Maintenance
2380
300
Cross Plains
Patio & Furniture
2545
550
Dickson
Patio & Furniture
1470
322
Fairview
Patio & Furniture
2655
743
Forest Hills
Landscape Design
2915
375
Franklin
Patio & Furniture
740
125
Gallatin
Landscape Design
985
325
Goodlettsville
Lawn & Maintenance
1555
435
Gordonsville
Lawn & Maintenance
5885
307
Hendersonville
Patio & Furniture
1315
368
La Vergne
Landscape Design
1120
314
Lebanon
Patio & Furniture
960
175
Mount Juliet
Lawn & Maintenance
1885
527
Murfreesboro
Lawn & Maintenance
5100
300
Portland
Landscape Design
1430
389
Smyrna
Lawn & Maintenance
1420
300
Spring Hill
Patio & Furniture
1460
460
Springfield
Landscape Design
1610
450
Third Quarter Mailings
Department/Group
Total Sent
Total Responses
Landscape Design
Lawn & Maintenance
Patio & Furniture
Total
Response Rate Determination
>20%
Excellent
15-20%
Very Good
11-15%
Good
0-11%
Average
City
# of Responses
Response Rate
City
City
City
Department # Mailed # Responses
Response
Rate
Department # Mailed # Responses
Response
Rate
Department # Mailed # Responses
Response
Rate
Classic Gardens and Landscape (CGL)
Dependent and Claim Information
ID #
Last Name
17 Lopez
5 Meandro
8 Easom
11 Calvert
14 Johnson
16 Pawlowski
21 Svenson
3 Ladewig
4 McDonald
12 Alverez
19 Yager
20 Hughes
2 Stewart
6 Jonas
10 Artagnan
15 Alvarez
18 Gaylord
7 Henry
9 Ohlberg
1 Conrad
First Name
Anita
Juan
Nassar
Maria
Burton
Jerry
Imre
Lisle
Alphonse
Beryl
Bobbi
Delores
Maureen
Keiko
George
José
David
Conrad
Sarah
Anna
Department
Lawn & Maintenance
Patio & Furniture
Lawn & Maintenance
Landscape Design
Patio & Furniture
Landscape Design
Patio & Furniture
Lawn & Maintenance
Human Resources
Human Resources
Patio & Furniture
Human Resources
Human Resources
Patio & Furniture
Landscape Design
Landscape Design
Patio & Furniture
Patio & Furniture
Landscape Design
Lawn & Maintenance
# of Dependents, Brentwood, Landscape
# of Dependents, Springfield, Human Resources
# of Claims, Forest Hills, Patio
# of Claims, Gallatin, Lawn & Maintenance
City
Brentwood
Springfield
Forest Hills
Gallatin
Forest Hills
Brentwood
Brentwood
Forest Hills
Brentwood
Springfield
Brentwood
Gallatin
Springfield
Gallatin
Gallatin
Brentwood
Forest Hills
Brentwood
Brentwood
Brentwood
Gardens and Landscape (CGL)
ndent and Claim Information
# of
Dependents # of Claims
4
3
5
2
1
1
2
0
3
4
4
3
3
1
5
1
1
1
2
0
3
2
4
2
2
0
1
0
2
4
3
5
4
2
2
1
3
1
1
2
Classic Gardens and Landscape (CGL)
Employee Full Names
ID #
Last Name
1 Conrad
2 Stewart
3 Ladewig
4 McDonald
5 Meandro
6 Jonas
7 Henry
8 Easom
9 Ohlberg
10 Artagnan
11 Calvert
12 Alverez
14 Johnson
15 Alvarez
16 Pawlowski
17 Lopez
18 Gaylord
19 Yager
20 Hughes
21 Svenson
First Name
Anna
Maureen
Lisle
Alphonse
Juan
Keiko
Conrad
Nassar
Sarah
George
Maria
Beryl
Burton
José
Jerry
Anita
David
Bobbi
Delores
Imre
Middle Name
Maria
Susan
Anthony
Juan
Pedro
Jai
George
Atta
Jean
Pierre
Elena
Lynne
James
Rafael
David
Patricia
Robert
Jane
Elizabeth
Oscar
Name as Shown on Policy
5/31/24, 11:33 AM
Excel 6 – Project 1 – SIMnet
Print Info
Student Name: Brown, Kamen
Student ID: kbrow540
Username: kbrow540
Excel 6 – Project 1
COURSE NAME CISM 2530 – SUMMER A 2024- Dyer | Summer A 2024
Class ends at 06/15/2024 11:59 PM
Start Date:05/06/202412:01 AMUS/Eastern
Due Date:06/02/202411:59 PMUS/Eastern
End Date:06/04/202411:59 PMUS/Eastern
Independent Project 6-5
Classic Gardens and Landscapes counts responses to specialty promotions to determine effectiveness. You use SUMIFS and
an IFS formula to complete the summary. You also calculate insurance statistics and display full names in one cell.
[Student Learning Outcomes 6.1, 6.2, 6.3, 6.6, 6.7]
File Needed: ClassicGardens-06.xlsx (Available from the Start File link.)
Completed Project File Name: [your name]-ClassicGardens-06.xlsx
Skills Covered in This Project
Nest MATCH and INDEX functions.
Create DSUM formulas.
Build an IFS function.
Build SUMIFS formulas.
Use TEXTJOIN to join labels.
Steps to complete This Project
Mark the steps as checked when you complete them.
1.
Open the ClassicGardens-06 start file. 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.
2. Create a nested INDEX and MATCH function to display the number of responses from a city.
a.
Click the Mailings sheet tab and select and name cells A3:D28 as Responses.
b.
Click the Mailing Stats sheet tab.
c.
Click cell B21 and type Carthage.
d.
Click cell C21, start an INDEX function, and select the first argument list option.
e.
Choose or type the Responses range name for the Array argument.
f.
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.
g.
h.
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-106) and click OK. The result displays as 343.00.
https://georgiasouthern.desire2learn.com/d2l/le/content/766537/viewContent/12260820/View
1/3
5/31/24, 11:33 AM
Excel 6 – Project 1 – SIMnet
Figure 6-106 Nested MATCH and INDEX functions
i.
Format the results to show zero decimal places.
j.
Type Smyrna in cell B21.
3. Use DSUM to summarize mailing data.
a.
Select the Mailings sheet and note that number mailed is located in the third column and response data is in the
fourth column.
b.
Click the Criteria sheet tab. Select cell B2 and type lan* to select data for the Landscape Design department.
c.
Type law* in cell B5 for the Lawn & Maintenance department.
d.
Type pat* in cell B8 for the Patio & Furniture department.
e.
Click the Mailing Stats sheet tab and select cell B7.
f.
Use DSUM with the range name Responses as the Database argument. Type 3 for the Field argument (# Mailed
column), and enter an absolute reference to cells B1:B2 on the Criteria sheet as the Criteria argument.
g.
Copy the formula to cell C7 and edit the Field argument to use the fourth column (# Responses).
h.
Use DSUM in cells B8:C9 to calculate results for the two remaining departments.
4.
Use SUM in cells B10:C10.
5.
Format all values as Comma Style with no decimal places.
6. Create an IFS function to display a response rating. Note: If your version of Excel does not include the IFS function, build
the following nested IF function =IF(C7/B7>=20%,$C$15,IF(C7/B7>=15%,$C$16,IF(C7/B7>=11%,$C$17,$C$18))) to
display the rating.
a.
Click cell D7. The response rate and ratings are shown in rows 14:18.
b.
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.
c.
Click the Value_if_true1 box, select C15, and press F4 (FN+F4) (Figure 6-107).
Figure 6-107 First Logical_test and Value_if_true arguments
d.
Click the Logical_test2 box, select C7, type /, select cell B7, and type >=15%.
e.
Click the Value_if_true2 box, click cell C16, and press F4 (FN+F4).
f.
Complete the third and fourth logical tests and Value_if_true arguments (Figure 6-108).
g.
Copy the formula in cell D7 to cells D8:D10.
7. Use SUMIFS to total insurance claims and dependents by city
and department.
a.
Click the Employee Insurance sheet tab and select cell
E25.
b.
Use SUMIFS with an absolute reference to cells F4:F23
as the Sum_range argument.
c.
The Criteria_range1 argument is an absolute reference
to cells E4:E23. The Criteria1 argument is bre* to select
the city of Brentwood.
https://georgiasouthern.desire2learn.com/d2l/le/content/766537/viewContent/12260820/View
Figure 6-108 Completed IFS function arguments
2/3
5/31/24, 11:33 AM
Excel 6 – Project 1 – SIMnet
d.
The Criteria_range2 argument is an absolute reference to cells D4:D23, the department column, with criteria of lan*
to select the Landscape Design department.
e.
Click OK. The result for cell E25 is 10.
f.
Build SUMIFS formulas for cells E26:E28 based on the criteria displayed in cells C26:C28.
g.
Format borders to remove inconsistencies, if any, and adjust column widths to display data.
8. Use TEXTJOIN to display names. Note: If your version of Excel does not include the TEXTJOIN function, use CONCAT or
CONCATENATE to build the function.
a.
Click the Full Names sheet tab and select cell E4.
b.
Start a TEXTJOIN function and press Spacebar for the Delimiter argument.
c.
Click the Text1 box and select cell C4.
d.
Complete the Text2 and Text3 arguments to show middle and last names and click OK (Figure 6-109).
Figure 6-109 Delimiter is a space
e.
9.
Copy the formula to display full names in column E.
Save and close the ClassicGardens-06 Excel workbook (Figure 6-110).
Figure 6-110 Completed worksheets for Excel 6-5
10.
Upload and save the ClassicGardens-06 Excel workbook.
11.
Submit project for grading.
https://georgiasouthern.desire2learn.com/d2l/le/content/766537/viewContent/12260820/View
3/3