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.
- 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.
- Create a nested INDEX and MATCH employment to flaunt the reckon of vindications from a city.
- Click the Mailings shuffle tab and chosen and call cells A3:D28 as Responses.
- Click the Mailing Stats shuffle tab.
- Click cell B21 and model Carthage.
- Click cell C21, begin an INDEX employment, and chosen the earliest discussion register liberty.
- Choose or model the Responses class call for the Array discussion.
- 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.
- 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.
- Click the Match_type box and model 0 (Figure 6-106) and click OK. The effect flaunts as 343.00.Figure 6-106 Nested MATCH and INDEX functions
- Format the effects to exhibition cipher decimal places.
- Type Smyrna in cell B21.
- Use DSUM to digest mailing axioms.
- Select the Mailings shuffle and music that reckon mailed is located in the third support and vindication axioms is in the fourth support.
- Click the Criteria shuffle tab. Chosen cell B2 and model lan* to chosen axioms for the Landscape Design portion.
- Type law* in cell B5 for the Lawn & Maintenance portion.
- Type pat* in cell B8 for the Patio & Furniture portion.
- Click the Mailing Stats shuffle tab and chosen cell B7.
- 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.
- Copy the formula to cell C7 and edit the Field discussion to use the fourth support (# Responses).
- Use DSUM in cells B8:C9 to weigh effects for the two cherishing portions.
- Use SUM in cells B10:C10.
- Format all values as Comma Style after a while no decimal places.
- 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.
- Click cell D7. The vindication rebuke and ratings are exhibitionn in rows 14:18.
- 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.
- Click the Value_if_true1 box, chosen C15, and force F4 (FN+F4) (Figure 6-107).Figure 6-107 First Logical_test and Value_if_true arguments
- Click the Logical_test2 box, chosen C7, model /, chosen cell B7, and model >=15%.
- Click the Value_if_true2 box, click cell C16, and force F4 (FN+F4).
- Complete the third and fourth controversyative standards and Value_if_true discussions (Figure 6-108).Figure 6-108 Completed IFS employment discussions
- Copy the formula in cell D7 to cells D8:D10.
- Use SUMIFS to entirety prophylactic claims and dependents by city and portion.
- Click the Employee Insurance shuffle tab and chosen cell E25.
- Use SUMIFS after a while an arbitrary allusion to cells F4:F23 as the Sum_range discussion.
- The Criteria_range1 discussion is an arbitrary allusion to cells E4:E23. The Criteria1 discussion is bre* to chosen the city of Brentwood.
- 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.
- Click OK. The effect for cell E25 is 10.
- Build SUMIFS formulas for cells E26:E28 installed on the criteria flaunted in cells C26:C28.
- Format borders to supforce inconsistencies, if any, and enjoin support widths to flaunt axioms.
- 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.
- Click the Full Names shuffle tab and chosen cell E4.
- Start a TEXTJOIN employment and force Spacebar for the Delimiter discussion.
- Click the Text1 box and chosen cell C4.
- Complete the Text2 and Text3 discussions to exhibition average and developed calls and click OK (Figure 6-109).Figure 6-109 Delimiter is a space
- Copy the formula to flaunt unmeasured calls in support E.
- Save and seal the ClassicGardens-06 Excel workbook (Figure 6-110).