5. c. 6. Lael is always on the lookout for qualified students who might be interested in running for office in student groups. A student is qualified to be an officer if they have already been elected to office in a student group; and if they have not, they are qualified if they have been certified financially. Populate the Officer Qualified column as follows: a. Enter a formula using nested functions and structured references to determine if the first student is qualified to be an officer. If the student has been previously elected (i.e., "Yes" displays in the Elected column), display Elected. b. Otherwise, if the student is "Finance Certified", display Yes; if not, display No. Fill the formula to the rest of the column, if necessary. Students who work with student organizations are also considered for employment at the Student Activities Office. Students with at least four years of post-secondary education are qualified for more complex Tier 2 jobs. Populate the final column as follows: Enter Tier as the final column heading. b. Enter a function and structured references to determine the work tier of the first student. If the student has completed at least four post-secondary years, display 2; if not, display 1. Fill the function to the rest of the column, if necessary. To the right of the large table, Lael has made a quick way to look up a student's name by their Student ID number. To make sure that the cell always displays a meaningful message, nest the existing function in another function to display Invalid Student ID if the VLOOKUP function returns an error result. Lael wants to determine several totals and averages for active students. In the appropriate cell, enter a function and structured references to count the total number of students if they have been elected to offices in student organizations. In the appropriate cell, use a function and structured references to determine the average number of post-secondary years for students who have been elected. a. C. 7. 8. 9. a M Student ID - Name Age Post-Secondary Years Base Rate Class Finance Certified Grad Student Elected Qualified Driver Leadership Training Mentor Officer Qualified colu G62918 Kay Colbert 24 6 16.5 2022 Yes Yes No YES YES Yes P49234 Michael Crozier 25 7 16.50 2023 Yes Yes Yes YES YES Yes W99035 Brandon Miles 21 3 15.75 2022 Yes No Yes NO YES Yes V42056 Michael Alvarez 22 4 15.75 2023 Yes No No NO YES Yes T59828 Ida Smith 19 2 15.75 2024 No No Yes NO YES No F72412 Betty Garza 24 6 16.50 2025 Yes Yes No YES YES Yes W20999 Leroy Pirkle 28 10 17.50 2022 Yes Yes No YES YES Yes Q18870 Warren Stewart 18 0 15.00 2026 No No No NO NO No W75774 Stacy Wiggins 24 6 16.50 2025 Yes Yes Yes YES YES Yes N25220 Billy Herald 21 3 15.75 2022 No No No NO YES Yes 094361 Margaret Cruz 23 5 16.50 2023 Yes No No NO YES Yes G87578 Chester Keese 18 0 0 15.00 2024 No No No NO NO No B68170 Claudette Littell 19 1 15.25 2026 Yes No No NO YES No M89375 Florence Miller 21 3 15.75 2023 Yes No No NO YES Yes S63900 Kimberly Gerace 26 8 17.50 2023 Yes Yes No YES YES Yes Y96036 Roman Goble 22 4 15.75 2025 Yes No No NO YES Yes C82505 Jocelyn Allen 22 4 4 15.75 2022 Yes No Yes NO YES Yes Y75358 Ron Morse 20 2 2 15.75 2022 Yes No No NO YES No J60811 Roberta Ervin 21 3 15.75 2026 No No No NO YES Yes Q56783 James Rusnak 19 1 15.25 2022 Yes No Yes NO YES No Q75281 Suzanne Lawrence 24 2 2 15.75 2025 Yes No Yes YES YES No V73596 Shannon Garner 18 0 15.00 2024 No No No NO NO No L86947 Emory Little 25 5 16.50 2023 Yes Yes Yes YES YES Yes W90960 Laura Li 24 6 16.50 2022 Yes Yes Yes YES YES P40886 Floretta Cauthen 18 0 15.00 2025 No No No NO NO No U88627 Beverly Berry 23 5 16.50 2023 Yes No No NO YES Yes Y88831 Stephanie Papa 19 1 15.25 2023 Yes No Yes NO YES No P83373 Wallace Rivera 29 9 17.50 2023 Yes Yes No YES YES Yes B53454 Larry Russell 19 1 15.25 2022 No No No NO NO No A60088 Aaron Sifford 25 7 16.50 2023 Yes Yes Yes YES YES Yes Yes 5. c. 6. Lael is always on the lookout for qualified students who might be interested in running for office in student groups. A student is qualified to be an officer if they have already been elected to office in a student group; and if they have not, they are qualified if they have been certified financially. Populate the Officer Qualified column as follows: a. Enter a formula using nested functions and structured references to determine if the first student is qualified to be an officer. If the student has been previously elected (i.e., "Yes" displays in the Elected column), display Elected. b. Otherwise, if the student is "Finance Certified", display Yes; if not, display No. Fill the formula to the rest of the column, if necessary. Students who work with student organizations are also considered for employment at the Student Activities Office. Students with at least four years of post-secondary education are qualified for more complex Tier 2 jobs. Populate the final column as follows: Enter Tier as the final column heading. b. Enter a function and structured references to determine the work tier of the first student. If the student has completed at least four post-secondary years, display 2; if not, display 1. Fill the function to the rest of the column, if necessary. To the right of the large table, Lael has made a quick way to look up a student's name by their Student ID number. To make sure that the cell always displays a meaningful message, nest the existing function in another function to display Invalid Student ID if the VLOOKUP function returns an error result. Lael wants to determine several totals and averages for active students. In the appropriate cell, enter a function and structured references to count the total number of students if they have been elected to offices in student organizations. In the appropriate cell, use a function and structured references to determine the average number of post-secondary years for students who have been elected. a. C. 7. 8. 9. a M Student ID - Name Age Post-Secondary Years Base Rate Class Finance Certified Grad Student Elected Qualified Driver Leadership Training Mentor Officer Qualified colu G62918 Kay Colbert 24 6 16.5 2022 Yes Yes No YES YES Yes P49234 Michael Crozier 25 7 16.50 2023 Yes Yes Yes YES YES Yes W99035 Brandon Miles 21 3 15.75 2022 Yes No Yes NO YES Yes V42056 Michael Alvarez 22 4 15.75 2023 Yes No No NO YES Yes T59828 Ida Smith 19 2 15.75 2024 No No Yes NO YES No F72412 Betty Garza 24 6 16.50 2025 Yes Yes No YES YES Yes W20999 Leroy Pirkle 28 10 17.50 2022 Yes Yes No YES YES Yes Q18870 Warren Stewart 18 0 15.00 2026 No No No NO NO No W75774 Stacy Wiggins 24 6 16.50 2025 Yes Yes Yes YES YES Yes N25220 Billy Herald 21 3 15.75 2022 No No No NO YES Yes 094361 Margaret Cruz 23 5 16.50 2023 Yes No No NO YES Yes G87578 Chester Keese 18 0 0 15.00 2024 No No No NO NO No B68170 Claudette Littell 19 1 15.25 2026 Yes No No NO YES No M89375 Florence Miller 21 3 15.75 2023 Yes No No NO YES Yes S63900 Kimberly Gerace 26 8 17.50 2023 Yes Yes No YES YES Yes Y96036 Roman Goble 22 4 15.75 2025 Yes No No NO YES Yes C82505 Jocelyn Allen 22 4 4 15.75 2022 Yes No Yes NO YES Yes Y75358 Ron Morse 20 2 2 15.75 2022 Yes No No NO YES No J60811 Roberta Ervin 21 3 15.75 2026 No No No NO YES Yes Q56783 James Rusnak 19 1 15.25 2022 Yes No Yes NO YES No Q75281 Suzanne Lawrence 24 2 2 15.75 2025 Yes No Yes YES YES No V73596 Shannon Garner 18 0 15.00 2024 No No No NO NO No L86947 Emory Little 25 5 16.50 2023 Yes Yes Yes YES YES Yes W90960 Laura Li 24 6 16.50 2022 Yes Yes Yes YES YES P40886 Floretta Cauthen 18 0 15.00 2025 No No No NO NO No U88627 Beverly Berry 23 5 16.50 2023 Yes No No NO YES Yes Y88831 Stephanie Papa 19 1 15.25 2023 Yes No Yes NO YES No P83373 Wallace Rivera 29 9 17.50 2023 Yes Yes No YES YES Yes B53454 Larry Russell 19 1 15.25 2022 No No No NO NO No A60088 Aaron Sifford 25 7 16.50 2023 Yes Yes Yes YES YES Yes Yes