Home Insert Draw Page Layout Formulas Data Review View Tell me Share o Comments Connections Properties V + Show Detail HA LO Data from Picture L AA 2 Sort ? Clear Reapply Advanced Stocks Currencies Automatic Geography Get Data Refresh All Anatomy Filter Consolidate Text to Columns Flash Fill Edit Links Remove Data Duplicates Validation What If Analysis Group Ungroup Subtotal - Hide Detail Analysis Tools Data Solver Analysis MAXIFS Xfx index B D E G H 1 K L M N o P Q R S T ART-39 Formulas Cell B3 Cell B4 Cell D8 J Summary Total Donations: Average donation: Total donors: Cell K2 State OH Last Name Schneider Beauclair Flores OH OH IL Wang OH OH OH IL OH OH A 1 Look Up Area 2 Donor ID: 3 Donor Position: 4 Donor Name: 5 Donor Amount: 6 7 College 8 ENGINEERING 9 BUSINESS 10 BUSINESS 11 ART 12 ENGINEERING 13 ART 14 ART 15 BUSINESS 16 ENGINEERING 17 ENGINEERING 18 ART 19 BUSINESS 20 ART 21 ART 22 BUSINESS 23 ART 24 ENGINEERING 25 BUSINESS 26 BUSINESS 27 ENGINEERING 28 ENGINEERING 29 ART 30 BUSINESS 31 ART 32 ENGINEERING 33 BUSINESS 34 ART 35 ENGINEERING 36 37 38 39 40 41 42 -indexl INDEX(array, row_num, (column_num]) INDEX(reference, row_num, (column_num], [area_num]) College ID Donor ID New Donor ID Full Name ENG 15 ENG-15 Schneider, Dedrick BUS 25 BUS-25 Beauclair, Oliver BUS 27 BUS-27 Flores, Adrian ART 45 ART-45 Wang, Shan ENG 22 ENG-22 Schafer, Elysia ART 39 ART-39 Romero, Samantha ART 12 ART-12 Mikhailov, Jon E13 BUS 14 BUS-14 Jaundreau, Hunter ENG 18 ENG-18 Kato, Eiji ENG 23 ENG-23 Schroder, Genevie ART 24 ART-24 Diaz, Sergio BUS 31 BUS-31 Lamoureux, Lucas ART 36 ART-36 Acosta, Jaime ART 42 ART-42 Ishikawa, Chie BUS 44 BUS-44 Kruger, Alfonso ART 38 ART-38 Normandeau, Amelia ENG 52 ENG-52 Cabrera, Mia BUS 53 BUS-53 Fernandez, Isabella BUS 49 BUS-49 Wright, Jerri ENG 47 ENG-47 Friedrich, Ava ENG 26 ENG-26 Byram, Emma ART 46 ART-46 Renault, Linden BUS 55 BUS-55 Sanchez, Caleb ART 41 ART-41 Zhang, Jun ENG 33 ENG-33 Deneau, Abigail BUS 58 BUS-58 Walker, Mitchell ART 34 ART-34 Perez, Tina ENG 58 ENG-58 Morgan, Terry PA OH PA City Columbus Cincinnati Akron Chicago Heights Cincinnati Dayton Columbus Chicago Toledo Akron Erie Toledo Pittsburgh Cincinnati Toledo Detroit Columbus San Francisco Bowling Green Anaheim Address 1243 W Abbey Rd 34 E Affinity Dr 912 E Julien Circle 2945 N Country Club Rd 259 S Wadsbury Dr 123 W Lincoln Ave 342 S Newton Dr 64 E 111th Place 41 W Eaton Drive 6124 N Odelle Dr 142 S Elm Street 3415 N Saddlewood Dr 1490 S Alcove Way 4152 E Dalbren Ln 641 W Banbury Dr 45 E Blackstone Court 923 W Haldy Rd 641 S Caiser Court 64 W Garden Court 15 E Oak Hill Drive 324 E Deer Valley Drive 3434 E Wallhaven Circle 25 W Catherine Street 3914 McCartney Rd 345 N Cobb Place 1850 N Lelanford Cir 45 E Keyes Ave 1200 E. Monticello Blvd Schafer Romero Mikhailov Jaundreau Kato Schroder Diaz Lamoureux Acosta Ishikawa Kruger Normandeau Cabrera Fernandez Wright Friedrich Byram Renault Sanchez Zhang Deneau Walker Perez Morgan OH College/School Engineering Business Business Art Engineering Art Art Business Engineering Engineering Art Business Art Art Business Art Engineering Business Business Engineering Engineering Art Business Art Engineering Business Art Engineering Donation 5,000 7,500 10,000 3,500 4,000 6,000 750 500 1,250 3,000 1,255 3,000 855 6,500 2,500 5,555 3,545 4,210 900 950 11,000 12,500 2,355 900 1,600 655 6,545 750 OH MI OH CA OH CA Park City UT OH Akron Ann Arbor Youngstown Detroit Bowling Green Cleveland Cleveland MI OH MI OH OH OH Donors + Edit @ + 125% Tue Oct 12 7:14 PM Q 000 AutoSave Exp19_ExcelCh11_Cup_Donors_instructions Layout Tell me Home Insert Draw Design Layout References Mailings Review View Table Design Share Comments Arial AA A A = = 5 AaBbCD MERCEDES Head wasing A ASIDE. ab ADCDC Song E Pa BIUX, X 4 TE Engles Na space Styles Pame Dictate Editor Steps to Perform: Step Instructions Points Possible 8 Now you are ready to use the position number as an argument within the INDEX function. 5 In cell B4, insert an INDEX function that uses the range D8:K35, looks up the row position number from the MATCH function result, and then uses the column position number for Full Name. 9 5 In cell B5, insert an INDEX function that uses the range D8:K35, looks up the row position number from the MATCH function result, and then uses the column position number for Donation. 10 5 11 7 You want to format the results of the INDEX function. Format the value in cell B5 as Accounting Number Format with zero decimal places. To analyze the donor records, you are ready to create criteria and output ranges. You will enter conditions to find records for donors to the College of Business who donated $1,000 or more. Copy the range A7:K7 to cell A38 to create the column labels for the criteria range. Type Business in cell J39 and >=1000 in cell K39. 12 5 You are ready to create the output area and perform the advanced filter. Copy the column labels to cell A42. Perform the advanced filter by copying the records to the output area Now that you created a copy of the records meeting the conditions, you are ready to enter database functions in the Summary area 13 5 In cell K2, insert the database function to total the value of the donations for the records that meet the conditions in the criteria range. 14 5 In cell K3, insert the database function to calculate the average donation for the records that meet the conditions in the criteria range. Page 1 of 252 words TR 5A2 words English (United St Focus + 2074 Home Insert Draw Page Layout Formulas Data Review View Tell me Share o Comments Connections Properties V + Show Detail HA LO Data from Picture L AA 2 Sort ? Clear Reapply Advanced Stocks Currencies Automatic Geography Get Data Refresh All Anatomy Filter Consolidate Text to Columns Flash Fill Edit Links Remove Data Duplicates Validation What If Analysis Group Ungroup Subtotal - Hide Detail Analysis Tools Data Solver Analysis MAXIFS Xfx index B D E G H 1 K L M N o P Q R S T ART-39 Formulas Cell B3 Cell B4 Cell D8 J Summary Total Donations: Average donation: Total donors: Cell K2 State OH Last Name Schneider Beauclair Flores OH OH IL Wang OH OH OH IL OH OH A 1 Look Up Area 2 Donor ID: 3 Donor Position: 4 Donor Name: 5 Donor Amount: 6 7 College 8 ENGINEERING 9 BUSINESS 10 BUSINESS 11 ART 12 ENGINEERING 13 ART 14 ART 15 BUSINESS 16 ENGINEERING 17 ENGINEERING 18 ART 19 BUSINESS 20 ART 21 ART 22 BUSINESS 23 ART 24 ENGINEERING 25 BUSINESS 26 BUSINESS 27 ENGINEERING 28 ENGINEERING 29 ART 30 BUSINESS 31 ART 32 ENGINEERING 33 BUSINESS 34 ART 35 ENGINEERING 36 37 38 39 40 41 42 -indexl INDEX(array, row_num, (column_num]) INDEX(reference, row_num, (column_num], [area_num]) College ID Donor ID New Donor ID Full Name ENG 15 ENG-15 Schneider, Dedrick BUS 25 BUS-25 Beauclair, Oliver BUS 27 BUS-27 Flores, Adrian ART 45 ART-45 Wang, Shan ENG 22 ENG-22 Schafer, Elysia ART 39 ART-39 Romero, Samantha ART 12 ART-12 Mikhailov, Jon E13 BUS 14 BUS-14 Jaundreau, Hunter ENG 18 ENG-18 Kato, Eiji ENG 23 ENG-23 Schroder, Genevie ART 24 ART-24 Diaz, Sergio BUS 31 BUS-31 Lamoureux, Lucas ART 36 ART-36 Acosta, Jaime ART 42 ART-42 Ishikawa, Chie BUS 44 BUS-44 Kruger, Alfonso ART 38 ART-38 Normandeau, Amelia ENG 52 ENG-52 Cabrera, Mia BUS 53 BUS-53 Fernandez, Isabella BUS 49 BUS-49 Wright, Jerri ENG 47 ENG-47 Friedrich, Ava ENG 26 ENG-26 Byram, Emma ART 46 ART-46 Renault, Linden BUS 55 BUS-55 Sanchez, Caleb ART 41 ART-41 Zhang, Jun ENG 33 ENG-33 Deneau, Abigail BUS 58 BUS-58 Walker, Mitchell ART 34 ART-34 Perez, Tina ENG 58 ENG-58 Morgan, Terry PA OH PA City Columbus Cincinnati Akron Chicago Heights Cincinnati Dayton Columbus Chicago Toledo Akron Erie Toledo Pittsburgh Cincinnati Toledo Detroit Columbus San Francisco Bowling Green Anaheim Address 1243 W Abbey Rd 34 E Affinity Dr 912 E Julien Circle 2945 N Country Club Rd 259 S Wadsbury Dr 123 W Lincoln Ave 342 S Newton Dr 64 E 111th Place 41 W Eaton Drive 6124 N Odelle Dr 142 S Elm Street 3415 N Saddlewood Dr 1490 S Alcove Way 4152 E Dalbren Ln 641 W Banbury Dr 45 E Blackstone Court 923 W Haldy Rd 641 S Caiser Court 64 W Garden Court 15 E Oak Hill Drive 324 E Deer Valley Drive 3434 E Wallhaven Circle 25 W Catherine Street 3914 McCartney Rd 345 N Cobb Place 1850 N Lelanford Cir 45 E Keyes Ave 1200 E. Monticello Blvd Schafer Romero Mikhailov Jaundreau Kato Schroder Diaz Lamoureux Acosta Ishikawa Kruger Normandeau Cabrera Fernandez Wright Friedrich Byram Renault Sanchez Zhang Deneau Walker Perez Morgan OH College/School Engineering Business Business Art Engineering Art Art Business Engineering Engineering Art Business Art Art Business Art Engineering Business Business Engineering Engineering Art Business Art Engineering Business Art Engineering Donation 5,000 7,500 10,000 3,500 4,000 6,000 750 500 1,250 3,000 1,255 3,000 855 6,500 2,500 5,555 3,545 4,210 900 950 11,000 12,500 2,355 900 1,600 655 6,545 750 OH MI OH CA OH CA Park City UT OH Akron Ann Arbor Youngstown Detroit Bowling Green Cleveland Cleveland MI OH MI OH OH OH Donors + Edit @ + 125% Tue Oct 12 7:14 PM Q 000 AutoSave Exp19_ExcelCh11_Cup_Donors_instructions Layout Tell me Home Insert Draw Design Layout References Mailings Review View Table Design Share Comments Arial AA A A = = 5 AaBbCD MERCEDES Head wasing A ASIDE. ab ADCDC Song E Pa BIUX, X 4 TE Engles Na space Styles Pame Dictate Editor Steps to Perform: Step Instructions Points Possible 8 Now you are ready to use the position number as an argument within the INDEX function. 5 In cell B4, insert an INDEX function that uses the range D8:K35, looks up the row position number from the MATCH function result, and then uses the column position number for Full Name. 9 5 In cell B5, insert an INDEX function that uses the range D8:K35, looks up the row position number from the MATCH function result, and then uses the column position number for Donation. 10 5 11 7 You want to format the results of the INDEX function. Format the value in cell B5 as Accounting Number Format with zero decimal places. To analyze the donor records, you are ready to create criteria and output ranges. You will enter conditions to find records for donors to the College of Business who donated $1,000 or more. Copy the range A7:K7 to cell A38 to create the column labels for the criteria range. Type Business in cell J39 and >=1000 in cell K39. 12 5 You are ready to create the output area and perform the advanced filter. Copy the column labels to cell A42. Perform the advanced filter by copying the records to the output area Now that you created a copy of the records meeting the conditions, you are ready to enter database functions in the Summary area 13 5 In cell K2, insert the database function to total the value of the donations for the records that meet the conditions in the criteria range. 14 5 In cell K3, insert the database function to calculate the average donation for the records that meet the conditions in the criteria range. Page 1 of 252 words TR 5A2 words English (United St Focus + 2074