excel independent project 6-5


Classic Gardens and Landscapes counts vindications to point promotions to indicate virtue. You use SUMIFS and an IFS formula to entirety the abridgment. You as-well weigh prophylactic statistics and flaunt unmeasured calls in one cell.

[Student Learning Outcomes 6.1, 6.2, 6.3, 6.6, 6.7]

File Needed: ClassicGardens-06.xlsx (Available from the Begin Refine link.)

Completed Purpose Refine Name: [your call]-ClassicGardens-06.xlsx

Skills Covered in This Project

  • Nest MATCH and INDEX employments.
  • Create DSUM formulas.
  • Build an IFS employment.
  • Build SUMIFS formulas.
  • Use TEXTJOIN to associate labels.
  1. Open the ClassicGardens-06 begin refine. The refine obtain be renamed automatically to embody your call. Change the purpose refine call if directed to do so by your schoolmistress, and secure it.
  2. Create a nested INDEX and MATCH employment to flaunt the reckon of vindications from a city.
    1. Click the Mailings shuffle tab and chosen and call cells A3:D28 as Responses.
    2. Click the Mailing Stats shuffle tab.
    3. Click cell B21 and model Carthage.
    4. Click cell C21, begin an INDEX employment, and chosen the earliest discussion register liberty.
    5. Choose or model the Responses class call for the Array discussion.
    6. Click the Row_num box and nest a MATCH employment. Chosen cell B21 for the Lookup_value and cells A3:A28 on the Mailings shuffle for the Lookup_array. Click the Match_type discussion box and model 0.
    7. Click INDEX in the Formula bar. Click the Column_num box and nest a second MATCH employment to appear up cell D3 on the Mailings shuffle in the appearup adorn A3:D3.
    8. Click the Match_type box and model 0 (Figure 6-106) and click OK. The effect flaunts as 343.00.MATCH statements are the row_num and col_num discussions.Figure 6-106 Nested MATCH and INDEX functions
    9. Format the effects to exhibition cipher decimal places.
    10. Type Smyrna in cell B21.
  3. Use DSUM to digest mailing axioms.
    1. Select the Mailings shuffle and music that reckon mailed is located in the third support and vindication axioms is in the fourth support.
    2. Click the Criteria shuffle tab. Chosen cell B2 and model lan* to chosen axioms for the Landscape Design portion.
    3. Type law* in cell B5 for the Lawn & Maintenance portion.
    4. Type pat* in cell B8 for the Patio & Furniture portion.
    5. Click the Mailing Stats shuffle tab and chosen cell B7.
    6. Use DSUM after a while the class call Responses as the Database discussion. Model 3 for the Field discussion (# Mailed support), and invade an arbitrary allusion to cells B1:B2 on the Criteria shuffle as the Criteria discussion.
    7. Copy the formula to cell C7 and edit the Field discussion to use the fourth support (# Responses).
    8. Use DSUM in cells B8:C9 to weigh effects for the two cherishing portions.
  4. Use SUM in cells B10:C10.
  5. Format all values as Comma Style after a while no decimal places.
  6. Create an IFS employment to flaunt a vindication rating. Note: If your rendering of Excel does not embody the IFS function, uplift the forthcoming nested IF function =IF(C7/B7>=20%,$C$15,IF(C7/B7>=15%,$C$16,IF(C7/B7>=11%,$C$17,$C$18))) to flaunt the rating.
    1. Click cell D7. The vindication rebuke and ratings are exhibitionn in rows 14:18.
    2. Start an IFS employment and chosen C7 for the Logical_test1 discussion. Model / for dissolution and chosen cell B7. Model >=20% to entirety the standard.
    3. Click the Value_if_true1 box, chosen C15, and force F4 (FN+F4) (Figure 6-107).Each controversyative_standard discussion has a selfsame value_if_true discussion.Figure 6-107 First Logical_test and Value_if_true arguments
    4. Click the Logical_test2 box, chosen C7, model /, chosen cell B7, and model >=15%.
    5. Click the Value_if_true2 box, click cell C16, and force F4 (FN+F4).
    6. Complete the third and fourth controversyative standards and Value_if_true discussions (Figure 6-108).Several discussions are scrolled out of viewFigure 6-108 Completed IFS employment discussions
    7. Copy the formula in cell D7 to cells D8:D10.
  7. Use SUMIFS to entirety prophylactic claims and dependents by city and portion.
    1. Click the Employee Insurance shuffle tab and chosen cell E25.
    2. Use SUMIFS after a while an arbitrary allusion to cells F4:F23 as the Sum_range discussion.
    3. The Criteria_range1 discussion is an arbitrary allusion to cells E4:E23. The Criteria1 discussion is bre* to chosen the city of Brentwood.
    4. The Criteria_range2 discussion is an arbitrary allusion to cells D4:D23, the portion support, after a while criteria of lan* to chosen the Landscape Design portion.
    5. Click OK. The effect for cell E25 is 10.
    6. Build SUMIFS formulas for cells E26:E28 installed on the criteria flaunted in cells C26:C28.
    7. Format borders to supforce inconsistencies, if any, and enjoin support widths to flaunt axioms.
  8. Use TEXTJOIN to flaunt calls. Note: If your rendering of Excel does not embody the TEXTJOIN function, use CONCAT or CONCATENATE to uplift the employment.
    1. Click the Full Names shuffle tab and chosen cell E4.
    2. Start a TEXTJOIN employment and force Spacebar for the Delimiter discussion.
    3. Click the Text1 box and chosen cell C4.
    4. Complete the Text2 and Text3 discussions to exhibition average and developed calls and click OK (Figure 6-109).The extract marks flaunt when you affect to the direct beginning box in the register.Figure 6-109 Delimiter is a space
    5. Copy the formula to flaunt unmeasured calls in support E.
  9. Save and seal the ClassicGardens-06 Excel workbook (Figure 6-110).Completed worksheets for Excel 6-5