Your business fraternity recently held a half-marathon to raise money for the local animal shelter. You have been asked to analyze the results from the fundraiser. The fundraising committee and executive board would like to post results showing each runner, the runner’s age group, pace, finish time, and any awards the runner has received. Awards are based on overall place, age group place, or amount of donations collected.
1. Write a function to extract first name of participants. You need to write the function only for cell B2 and explain your answer.(20 points)
Hint:
• There is an underscore between fist name and last name of participants
• You need to use Left and Find functions to solve this problem
2. Write a function to extract last name of participants. You need to write the function only for cell C2 and explain your answer.(20 points)
Hint:
• there is an underscore between fist name and last name of participants
• You need to use Right, LEN and FIND functions to solve this problem
3. In Donor Level, create a formula (Hint : use a VLOOKUP function) with an approximate match to determine the donation level each runner has met based on the Amount Raised column and the Donor Leveltablein the Lookup Table worksheet. You need to write the function for cell J2 and explain your answer (20 points)
4. In Place Award, write a function (Hint:use IF and OR functions) to determine whether a runner will be eligible for a place award. A place award is for any runners who place first or second in their Age Group or first, second, or third in the Overall Place. If a runner qualifies for a place award, the result should be “Award”. If a runner does not qualify for a place award, the cell should be left blank. You just need to write the function in Cell K2 and explain your answer (20 points)
5. After finishing Question 1 to Question 4 of this assignment, your supervisor asked you to format Participants worksheet in a way that participants could be sorted by Amount Raised column. Explain your approach to solve this question.(20 points)
participatnts name First Name Last Name Age Gender Age Group Overall Place Age Group Place Amount Raised Donor Level Place Award
Abbie_Brimmer 24 F 3 2 $300
Ciera_Torrance 25 F 4 4 $1,400
Sara_Clendenen 41 F 5 2 $1,500
Brooke_Caron 23 F 6 5 $2,500
Jesse_Gilmore 33 F 7 3 $650
Zoe_Brenton 20 F 9 7 $510
Carol_Steward 41 F 11 5 $200
Tracy_Jones 27 F 14 9 $100
Jane_Tuttle 48 F 16 1 $200
Samantha_Stout 23 F 17 11 $300
Riley_Fishback 29 F 21 14 $1,100
Liz_O’Conner 18 F 22 15 $360
Ava_Flanigan 28 F 25 18 $100
Suzie_Knabe 34 F 28 9 $250
Ann_Williams 25 F 31 19 $600
Lorraine_Sindy 40 F 33 12 $160
Amy_Blokey 30 F 34 13 $175
Kristine_Cain 48 F 35 4 $3,150
Kali_Krull 53 F 38 5 $630
Emily_Roundmond 50 F 40 6 $490
Julie_Luton 21 F 43 23 $210
Rhonda_Mason 19 F 44 24 $150
Katie_Henderson 23 F 45 25 $175
Nia_Mitchell 48 F 52 7 $610
Madelyn_Toth 59 F 53 8 $250
Pam_Helman 67 F 54 1 $200
Sara_Bronkley 29 F 55 31 $100
Katie_Clark 19 F 60 35 $120
Samantha_Hills 22 F 61 36 $125
Jonathan_Bratcher 19 F 62 37 $320
Abbi_Kendrick 41 F 65 17 $1,500
Donna_Jones 65 F 67 2 $2,100
Rachel_Montgomery 63 F 69 10 $200
Sharon_Hoskins 21 F 73 40 $2,200
Joe_Scotten 23 M 1 1 $1,200
Shawn_Storms 32 M 2 1 $650
Rowben_Davis 24 M 8 6 $890
Nick_Young 42 M 10 4 $480
Trevor_Engel 18 M 12 8 $150
Zane_Finley 34 M 13 6 $100
Ryan_Angelo 23 M 15 10 $100
Ethan_Rose 19 M 18 12 $250
James_Moritz 43 M 19 7 $620
Kaden_Kennedy 24 M 20 13 $325
Scott_Bell 19 M 23 16 $520
Richard_Andrews 23 M 24 17 $100
Nathan_Kirks 46 M 26 2 $100
Adam_Pickett 34 M 27 8 $320
Blake_Knight 30 M 29 10 $850
Jon_Korben 42 M 30 11 $400
Percy_Monroe 48 M 32 3 $320
Horath_Janes 23 M 36 20 $200
Kade_Stapinkski 24 M 37 21 $220
Mike_Krutz 31 M 39 14 $650
Jason_Lemieux 43 M 41 15 $3,000
Reid_Lheureau 28 M 42 22 $750
Justin_McMillan 20 M 46 26 $320
Bruce_Gallo 20 M 47 27 $250
Riley_Gilliam 31 M 48 16 $600
Martin_Kramer 28 M 49 28 $190
Joseph_Heidegger 22 M 50 29 $580
Brian_Shepherd 25 M 51 30 $3,000
Todd_Kapp 18 M 56 32 $100
Shane_Mroz 48 M 57 9 $3,100
Romans_Ortoleva 23 M 58 33 $100
Brad_Wagner 23 M 59 34 $350
James_Frommer 18 M 63 38 $410
Jack_Wearner 26 M 64 39 $100
Victor_Heckler 30 M 66 18 $500
Brock_Roper 37 M 68 19 $400
Neil_Troyer 44 M 70 20 $950
Brock_Richardson 39 M 71 21 $620
Dan_Lukens 47 M 72 11 $450
Rusty_Jones 67 M 74 3 $100