You want to count the number of applications that meet two more conditions. In cell H6, insert the COUNTIFS function to count the number of applications that meet two conditions: Residences in the range B11:B67 are In-State and Final Decisions in the range J11:J67 are Early Admissions (cell E3).
15	You want to identify the highest score based on two conditions. In cell H7, insert the MAXIFS function to identify the highest score in the range I11:I67 that meets two conditions: Residences in the range B11:B67 are In-State and Final Decisions in the range J11:J67 are Early Admissions (E3).
16	In cell H8, insert the AVERAGEIFS function to calculate the average score in the range I11:I67 that meets two conditions: Residences in the range B11:B67 are In-State and Final Decisions in the range J11:J67 are *Adm*. Use the asterisks as wildcards so that it includes both Early Admission and Admit.
17	In cell I5, insert the AVERAGEIFS function to calculate the average SAT score that meets two conditions: Scores in the range I11:I67 are greater than or equal to 3500, and Final Decisions in the range J11:J67 are Early Admissions (cell E3). Use mixed references appropriately.
18	In cell I6, insert the AVERAGEIFS function to calculate the average SAT score that meets two conditions: Residences in the range B11:B67 are In-State and Final Decisions in the range J11:J67 are Early Admissions (cell E3). Use mixed references appropriately.
19	Copy the functions from the range I5:I6 to the range J5:J6.
20	You want to insert a map that depicts admissions by state. Display the Map worksheet. Create a map chart from the range A1:B5. Change the chart title to Admissions by State. Display the Format Data Series task pane and select Only regions with data as the map area.
21	You want to place the top-left corner of the map in cell C1 and change the map size. Cut the map and paste it into cell C1. Set 3.12" height and 3.26" width for the map.
22	Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets.
23	Save and close Exp19_Excel_Ch07_ML1_Admissions.xlsx. Exit Excel. Submit the file as directed.

 

Transcribed Image Text:

File Home Insert Page Layout Formulas Data Review View Add-ins Help B Share O Comments Insert Σ Arial v 10 A A" 2 Wrap Text General DX Delete v Paste Conditional Format as Cell Sort & Find & Analyze = = Merge & Center v $ - % 9 68 8 B I U v Formatting v Table v Styles v HFormat v Filter v Select v Data Clipboard Font Alignment Number Styles Cells Editing Analysis A1 fr Admission Criteria A D F G H J K M Summary Data Description Early Admission Admit Score >= 3500 and Early Admissions In State Early Admissions Highest In-State Score Early Admissions Average In-State Score All Admissions 1 Admission Criteria 2 Standard Early Admit Early Reject Counts Average SAT Average GPA 3 SAT 4 GPA 1400 800 3.80 1.80 (regular admission) Input Area 2900 6 Threshold Score 7 Multiplier 8 Initial Deadline 600 2021-03-01 9. Days Date Before 10 ID Residence Received Deadline SAT GPA Admit Early Reject Early Score Final Decision 11 2020005 Out State 2020-11-15 1525 4.00 2020035 In State 2020040 In State 2020330 In State 2020335 In State 12 2020-11-15 1483 3.73 13 2020-11-15 874 3.26 14 2020-11-30 1163 1225 3.53 15 2020-11-30 3.58 16 2020340 Out State 2020345 In State 2020350 In State 2020355 In State 2020-11-30 925 3.04 17 2020-11-30 1545 4.00 18 2020-11-30 1397 3.32 19 2020-11-30 855 3.39 20 2020360 Out State 2020-11-30 1400 3.26 21 2020395 In State 2020-11-30 725 2.42 22 2020460 Out State 2020-12-01 855 2.75 23 2020465 In State 2020-12-01 1341 361 Applicants Map Ready 100% 用 File Home Insert Page Layout Formulas Data Review View Add-ins Help B Share O Comments Insert Σ Arial v 10 A A" 2 Wrap Text General DX Delete v Paste Conditional Format as Cell Sort & Find & Analyze = = Merge & Center v $ - % 9 68 8 B I U v Formatting v Table v Styles v HFormat v Filter v Select v Data Clipboard Font Alignment Number Styles Cells Editing Analysis A1 fr Admission Criteria A D F G H J K M Summary Data Description Early Admission Admit Score >= 3500 and Early Admissions In State Early Admissions Highest In-State Score Early Admissions Average In-State Score All Admissions 1 Admission Criteria 2 Standard Early Admit Early Reject Counts Average SAT Average GPA 3 SAT 4 GPA 1400 800 3.80 1.80 (regular admission) Input Area 2900 6 Threshold Score 7 Multiplier 8 Initial Deadline 600 2021-03-01 9. Days Date Before 10 ID Residence Received Deadline SAT GPA Admit Early Reject Early Score Final Decision 11 2020005 Out State 2020-11-15 1525 4.00 2020035 In State 2020040 In State 2020330 In State 2020335 In State 12 2020-11-15 1483 3.73 13 2020-11-15 874 3.26 14 2020-11-30 1163 1225 3.53 15 2020-11-30 3.58 16 2020340 Out State 2020345 In State 2020350 In State 2020355 In State 2020-11-30 925 3.04 17 2020-11-30 1545 4.00 18 2020-11-30 1397 3.32 19 2020-11-30 855 3.39 20 2020360 Out State 2020-11-30 1400 3.26 21 2020395 In State 2020-11-30 725 2.42 22 2020460 Out State 2020-12-01 855 2.75 23 2020465 In State 2020-12-01 1341 361 Applicants Map Ready 100% 用

Answer rating: 100% (QA)

14 Click on the cell H6 enter the formula COUNTIFSB11B67In StateJ11J67Ear... View the full answer