Classic Gardens and Landscape Excel Task

User Generated

Enpury2000

Business Finance

Description

Unformatted Attachment Preview

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. Click INDEX in the Formula bar and click the Insert Function (Fx) button. 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. h. Click the Match_type box and type 0 (Figure 6-106) and click Done. The result displays as 343.00. 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 + button to reveal the Value_if_true1 box, select C15, and press F4 (FN+F4) (Figure 6107). Figure 6-107 First Logical_test and Value_if_true arguments d. Click the + button to reveal the Logical_test2 box, select C7, type /, select cell B7, and type >=15%. Figure 6-108 Completed IFS function arguments e. Click the + to reveal 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. • The Criteria_range1 argument is an absolute reference to cells E4:E23. The Criteria1 argument is bre* to select the city of Brentwood. • 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. • Click Done. The result for cell E25 is 10. c. Complete SUMIFS formulas for cells E26:E28 using three-character criteria arguments to select each city. d. Format borders to remove inconsistencies, if any, and adjust column widths to display data. 8. Use TEXTJOIN to display names. 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 Done (Figure 6109). 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). 10. Upload and save the ClassicGardens-06 Excel workbook. 11. Submit project for grading. Figure 6-110 Completed worksheets for Excel 6-5 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-19% Very Good 11-14% Good 5-10% 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 # 17 5 8 11 14 16 21 3 4 12 19 20 2 6 10 15 18 7 9 1 Last Name Lopez Meandro Easom Calvert Johnson Pawlowski Svenson Ladewig McDonald Alverez Yager Hughes Stewart Jonas Artagnan Alvarez Gaylord Henry Ohlberg 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 # 1 2 3 4 5 6 7 8 9 10 11 12 14 15 16 17 18 19 20 21 Last Name Conrad Stewart Ladewig McDonald Meandro Jonas Henry Easom Ohlberg Artagnan Calvert Alverez Johnson Alvarez Pawlowski Lopez Gaylord Yager Hughes 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
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Done.Accept the question with a nice review.Just rename and upload

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

Gor...


Anonymous
Great! 10/10 would recommend using Studypool to help you study.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags