complete an Excel assignment

User Generated

yvhybatybir

Business Finance

Description

the requirements are in the picture 1-7 . 1. xls is the original work . the requirements are in the pictures and do it directly on the Excel. Please follow the steps and do it carefully

Unformatted Attachment Preview

Step 1 Download start file 1. Open the Classic Gardens-06 start file. Click the 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. 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 the Responses range 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. 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-105). MATCH 4 x V fx =INDEX(Responses,MATCH(B21, Mailings!A3:228,0),MATCH(Mailings!D3, Mailings!A3:03,0)) Formula Builder A B D x 1 2 Show All Functions Classic Gardens and Landscapes 3 Third Quarter Mailings 5 MATCH lookup_value = "# Responses" Mailings!D3 Total Sent Total Responses Response Rate 6 Department/Group 7 Landscape Design 8 Lawn & Maintenance 9 Patio & Furniture lookup_array {"City", "Department","#... Mailings!A3:D3 10 Total 11 12 13 14 match_type = 0 0 15 16 17 18 19 20 21 22 Response Rate Determination 20% and above Excellent 15% and above Very Good 10% and above Good Below 10% Average City I Carthage # of Responses D3,0)) Result: 343 Done 6-105 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. On the Mailings sheet, note that number sent 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. Click the Mailing Stats sheet tab and select cell B7. d. 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. e. Copy the formula to cell C7 and edit the Field argument to use the fourth column. f. Complete criteria for the two remaining departments on the Criteria sheet. g. Click the Mailing Stats sheet tab and select cell B8. h. Use DSUM in cells B8:C9 to calculate results for the two departments. 4. Use SUM in cells B10:C10. 5. Format all values as Comma Style with no decimal places. 6. Create a nested IF function to display a response rating. a. Click cell D7. The response rate and ratings are shown in rows 14:18. b. Start a nested IF function and type =IF(C7/B7>=20%, for the first logical_test and $C$15, for the first value_if_true (Figure 6-106). C. Type IF(C7/B7>=15%, for the second logical_test and $C$16, for the second value_if_true. X fx =IF(C7/B7>=20%,$C$15, А B с D E Classic Gardens and Landscapes Third Quarter Mailings Department/Group Landscape Design Lawn & Maintenance Patio & Furniture Total Sent 11,555 22,078 14,790 Total Responses Response Rate 2,824 !=1F(C7/87>=20%,$C$15,|| 2,847 3,591 Total 48,423 9,262 Response Rate Determination 20% and above [ Excellent 15% and above Very Good 10% and above Good Below 10% Average City # of Responses 300 Smyrna 6-106 First logical test and value_if_true arguments d. Type IF(C7/B7>=10%, for the third logical_test and $C$17, for the third value_if_true. e. Type IF(C7/B7=20%,$C$15,IF(C7/B7>=15%,$C$16,1F(C7/B7>=10%,$C$17,1F(C7/87
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

The solution is attached.The i...


Anonymous
Really helped me to better understand my coursework. Super recommended.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags