. . . . . Lazarus Consulting is a large computer consulting company in New York. Pete, the CEO and founder, is well known for his philanthropic efforts. Pete knows that most of his employees contribute to nonprofit organizations and wants to reward them for their efforts, while encouraging others to also contribute to charities. The only stipulations are that the charity must be nonprofit and the company will match only up to $2,000 per year per employee, with an award for anyone whose donations are over $1000. Pete also wants to complete an analysis on the donations and organizations frequented by his employees. He has provided 2 tabs for information and asks you to complete 4 more for a total of 6 tabs in the order specified below: Tracking Donations Donations Summary Sort Donations Auto Filter Donations Donations Subtotal Formatting Donations Pete requests that you do not deviate from the order specified above so their analysis software will be able to read in the values as needed without additional work on their part Instructions: 1) Open Ch4Analysis_LastName xlsx. 2) Save the spreadsheet as and Replace "Last Name" with your last name. 3) Never modify the Tracking Donations tab. Leave it where it is and as is. 4) All of the following tabs must appear in the order specified below as per client instructions, Tabs out of order will have a 5pts deduction per infraction The Donations Summary Tab 1) For the DONATIONS PER ORGANIZATION and DONATIONS PER EMPLOYEE tables, complete the averages and totals. MAKE SURE TO USE ABSOLUTE REFERENCES FOR YOUR RANGES! 2) For the averages, use the AVERAGEIF excel function 3) For the totals, use the SUMIF excel function 4) Any hard coding, le not using the functions specified in 2 and 3, will result in Opts for this part of the assignment. 5) Make sure all color formatting is reset if you drag your formulas. 350 F G H E B Submitted Employee Last Name First Name Organization Amount Donation Sent DONATIONS PER ORGANIZATION 12/21/2011 5200 12/12/2017 546 Daniels Julie Amnesty International Organization 2/21/2011 Average Total $200 Julie 22/2011 546 Daniels Amnesty International 6/21/2011 Amnesty International 546 Daniels 6/6/2010 5200 Julie Amnesty International Animal Shelter 9/21/2011 99/2011 849 Daniels $200 Jul Amnesty International 123/2011 Cancer Society 499 Cross Roberta 12/5/2011 Animal Shelter $20 657 Roger Scott 12012/2013 $35 Diabetic Association Animal Shelter 12/15/2011 23/2011 Habitat for Humanity 499 Cross Roberta 2/5/2011 Animal Shelter 520 212/2011 SCO 657 Rogers 2/15/2011 Homeless Sheltors Animal Shelter $35 6/3/2011 499 Cross Roberta Animal Shelter 520 6/5/2011 Leukemia Society 1 6/5/2011 657 Rogers Scott Animal Sheer $35 6/15/2011 Make a Wish Foundation 2 9/9/2011 499 Cross Roberta Animal Sheter 320 978/2011 Meals on Wheels 3 9/3/2011 987 Rogers Scott Animal Shelter $3a 9/18/2011 Police Federation 12/2011 243 Bagley Bob Cancer Society $25 12/6/2011 Vietnam Veterane 5 124/2011 389 Fisher Scom Cancer Society $125 12/10/2011 Grand Total 16 123/2011 456 Butler Amy Cancer Society 5250 12/10/2011 17 12/6/2011 812 Halte Todd Cancer Society $50 12/14/2011 DONATIONS PER EMPLOYEE 18 25/2011 243 Bagley Bob Cancer Society 525 2/6/2011 Last Name First Name Average Total 10 24/2011 189 Scott Cancer Society $25 2/10/2011 Ash Carlin 20 219/2011 450 Bor Amy Cancer Society $250 2/10/2011 Ash Jamin 21 27020111 12 Todd Cancer Society 2/14/2017 Ash Scott 2011 203 Bagley Bob Cancer Socery $25 66/2011 Bagley Bob 22 4/2017 300 sner SCOM Cancer Society 565 10/2011 Butler Amy 24 2011 4.6 Butter Amy Cancer Society 5250 6/10/2011 25 32 Hait: Cross 610/2011 Roberta Todd Cancer Society SLO /14/2011 26 Daniels Julio War2011 cancer Society 22 $20 91/2010 4/2011 Scott Fisher Scott Cancer Society $90 10/2011 20 9920111 489 butler Haim Am Cancer Society Gary 20 $2801 9710720111 2011 Hitz VE Todd Todd Cancer Society 30 10 12011 1472011 211 Smith Kelle James Diabetic Association 31 350 1211020111 125/2011 054Ah Can Diabetic Association LD Luteing 12.2011 $100 12/6/2011 675 Diabetic Association LOG Jim 33 12/07/2010 $100 97 Ham Cary 12/10/2011 Diabetic Association Rogers Scott James 122/2011 360 Diabetic Association Smith James 35 215/2011 650Ah $60 2/19/2011 Carlin Diabetic Association Williams Jimmy 30 21012011 675 $100 2/01/2011 Tacott 3 Diabetic Association 2017/2011 007 $100 Gary 30 2/18/2011 2011 Diabetic Association Grand Total 29 Smi $50 James 2022/2011 30 Diabec ASOS 05011 Can $50 6/10/2011 40 2011 Diabetic Association 675 Ah Scott 5100 41 Diabetic Association 6/6/2011 17/2011 987 Haim Gary $100 42 10/2011 2011 DISEASON 20 Smith $60 Diabesc Association 2011 602/2011 90 AS Carin 580 44 2011 9/19/2011 Diabetic Acion $100 45 Scol 1772011 9/2011 Debele Association 987 Him 45 Gary Diabec Association 9/18/2011 366 mith 580 Habitat for Human 122/2011 5150 1212312011 Sort Donations Auto Filter Donations Donations Subtotalna 23 dage 509 Fisher th MAS T: 2011 $100 me Donations Summary Ready $80 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 86 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 12/27/2011 11/29/2011 2/13/2011 2/27/2011 1/29/2011 6/13/2011 6/27/2011 5/29/2011 9/13/2011 9/27/2011 8/29/2011 12/27/2011 12/14/2011 2/27/2011 2/14/2011 6/27/2011 6/14/2011 9/27/2011 9/14/2011 12/6/2011 2/6/2011 6/6/2011 9/9/2011 12/16/2011 12/2/2011 12/16/2011 2/16/2011 2/2/2011 2/16/2011 6/16/2011 6/2/2011 6/16/2017 9/19/2011 9/2/2011 9/19/2011 12/2/2011 12/20/2011 2/2/2011 2/20/2011 6/2/2011 6/20/2011 9/2/2011 9/20/2011 12/10/2011 2/10/2011 6/10/2011 9/10/2011 B D E F G 370 Wills Mary Habitat for Humanity $100 12/29/2011 432 Ash Jamie Habitat for Humanity $50 12/1/2011 366 Smith James Habitat for Humanity $150 2/23/2011 370 Wills Mary Habitat for Humanity $100 2/29/2011 432 Ash Jamie Habitat for Humanity $50 2/1/2011 366 Smith James Habitat for Humanity $150 6/23/2011 370 Wills Mary Habitat for Humanity $100 6/29/2011 432 Ash Jamie Habitat for Humanity $50 6/1/2011 399 Smith James Habitat for Humanity $180 9/23/2011 370 Wills Mary Habitat for Humanity $100 9/29/2011 432 Ash Jamie Habitat for Humanity $80 9/1/2011 365 Lee Sufeing Homeless Shelters $25 12/30/2011 977 Rogers Scott Homeless Shelters $80 12/19/2011 365 Lee Sufeing Homeless Shelters $25 2/30/2011 977 Rogers Scott Homeless Shelters $80 2/19/2011 365 Lee Sufeing Homeless Shelters $25 6/30/2011 977 Rogers Scott Homeless Shelters 580 6/19/2011 398 Lee Sufeing Homeless Shelters $28 9/30/2011 977 Rogers Scott Homeless Shelters 9/19/2011 345 Cross Roberta Leukemia Society $60 12/19/2011 345 Cross Roberta Leukemia Society $60 2/19/2011 345 Cross Roberta Leukemia Society $60 6/19/2011 348 Cross Roberta Leukemia Society $90 9/19/2011 234 Daniels Julie Make a Wish Foundation $225 12/29/2011 454 Cross Roberta Make a Wish Foundation $75 12/4/2011 867 Daniels Julie Make a Wish Foundation $130 12/21/2011 234 Daniels Julie Make a Wish Foundation $225 2/29/2011 454 Cross Roberta Make a Wish Foundation 2/4/2011 867 Daniels Julie Make a Wish Foundation $130 2/21/2011 234 Daniels Julie Make a Wish Foundation $225 6/29/2011 454 Cross Roberta Make a Wish Foundation $751 6/4/2011 867 Daniels Julie Make a Wish Foundation $130 6/21/2011 234 Daniels Julie Make a Wish Foundation $228 9/29/2011 484 Cross Roberta Make a Wish Foundation $78 9/4/2011 897 Daniels Julie Make a Wish Foundation $130 9/21/2011 543 Fisher Scott Meals on Wheels $125 12/26/2011 558 LOS Jim Meals on Wheels $115 12/22/2011 543 Fisher Scott Meals on Wheels $25 2/26/2011 558 LOS Jim Meals on Wheels $15 2/22/2011 543 Fisher Scott Meals on Wheels $65 6/26/2011 558 LOS Jim Meals on Wheels $55 6/22/2011 843 Fisher Scott Meals on Wheels $98 9/29/2011 888 Los Jim Meals on Wheels $88 334 Kellee 9/22/2011 Pete Police Federation $180 334 Kellee 12/14/2011 Pete Police Federation $180 334 Kellee 2/14/2011 Pete Police Federation $180 6/14/2011 334 Kellee Pete Police Federation $180 9/14/2011 Donations Summary Sort Donations Auto Filter Donations Donatio $75 Ready 94 95 96 97 98 12/3/2011 213/2011 6/3/2011 9/3/2011 945 Williams 945 Williams 945 Williams 948 Williams Jimmy Jimmy Jimmy Jimmy Vietnam Veterans Vietnam Veterans Vietnam Veterans Vietnam Veterans $60 $60 560 $90 12/13/2011 2/13/2011 6/13/2011 9/13/2011 4 Donations Summary Sort Donations Auto Filter Donations Dor Ready . . . . . Lazarus Consulting is a large computer consulting company in New York. Pete, the CEO and founder, is well known for his philanthropic efforts. Pete knows that most of his employees contribute to nonprofit organizations and wants to reward them for their efforts, while encouraging others to also contribute to charities. The only stipulations are that the charity must be nonprofit and the company will match only up to $2,000 per year per employee, with an award for anyone whose donations are over $1000. Pete also wants to complete an analysis on the donations and organizations frequented by his employees. He has provided 2 tabs for information and asks you to complete 4 more for a total of 6 tabs in the order specified below: Tracking Donations Donations Summary Sort Donations Auto Filter Donations Donations Subtotal Formatting Donations Pete requests that you do not deviate from the order specified above so their analysis software will be able to read in the values as needed without additional work on their part Instructions: 1) Open Ch4Analysis_LastName xlsx. 2) Save the spreadsheet as and Replace "Last Name" with your last name. 3) Never modify the Tracking Donations tab. Leave it where it is and as is. 4) All of the following tabs must appear in the order specified below as per client instructions, Tabs out of order will have a 5pts deduction per infraction The Donations Summary Tab 1) For the DONATIONS PER ORGANIZATION and DONATIONS PER EMPLOYEE tables, complete the averages and totals. MAKE SURE TO USE ABSOLUTE REFERENCES FOR YOUR RANGES! 2) For the averages, use the AVERAGEIF excel function 3) For the totals, use the SUMIF excel function 4) Any hard coding, le not using the functions specified in 2 and 3, will result in Opts for this part of the assignment. 5) Make sure all color formatting is reset if you drag your formulas. 350 F G H E B Submitted Employee Last Name First Name Organization Amount Donation Sent DONATIONS PER ORGANIZATION 12/21/2011 5200 12/12/2017 546 Daniels Julie Amnesty International Organization 2/21/2011 Average Total $200 Julie 22/2011 546 Daniels Amnesty International 6/21/2011 Amnesty International 546 Daniels 6/6/2010 5200 Julie Amnesty International Animal Shelter 9/21/2011 99/2011 849 Daniels $200 Jul Amnesty International 123/2011 Cancer Society 499 Cross Roberta 12/5/2011 Animal Shelter $20 657 Roger Scott 12012/2013 $35 Diabetic Association Animal Shelter 12/15/2011 23/2011 Habitat for Humanity 499 Cross Roberta 2/5/2011 Animal Shelter 520 212/2011 SCO 657 Rogers 2/15/2011 Homeless Sheltors Animal Shelter $35 6/3/2011 499 Cross Roberta Animal Shelter 520 6/5/2011 Leukemia Society 1 6/5/2011 657 Rogers Scott Animal Sheer $35 6/15/2011 Make a Wish Foundation 2 9/9/2011 499 Cross Roberta Animal Sheter 320 978/2011 Meals on Wheels 3 9/3/2011 987 Rogers Scott Animal Shelter $3a 9/18/2011 Police Federation 12/2011 243 Bagley Bob Cancer Society $25 12/6/2011 Vietnam Veterane 5 124/2011 389 Fisher Scom Cancer Society $125 12/10/2011 Grand Total 16 123/2011 456 Butler Amy Cancer Society 5250 12/10/2011 17 12/6/2011 812 Halte Todd Cancer Society $50 12/14/2011 DONATIONS PER EMPLOYEE 18 25/2011 243 Bagley Bob Cancer Society 525 2/6/2011 Last Name First Name Average Total 10 24/2011 189 Scott Cancer Society $25 2/10/2011 Ash Carlin 20 219/2011 450 Bor Amy Cancer Society $250 2/10/2011 Ash Jamin 21 27020111 12 Todd Cancer Society 2/14/2017 Ash Scott 2011 203 Bagley Bob Cancer Socery $25 66/2011 Bagley Bob 22 4/2017 300 sner SCOM Cancer Society 565 10/2011 Butler Amy 24 2011 4.6 Butter Amy Cancer Society 5250 6/10/2011 25 32 Hait: Cross 610/2011 Roberta Todd Cancer Society SLO /14/2011 26 Daniels Julio War2011 cancer Society 22 $20 91/2010 4/2011 Scott Fisher Scott Cancer Society $90 10/2011 20 9920111 489 butler Haim Am Cancer Society Gary 20 $2801 9710720111 2011 Hitz VE Todd Todd Cancer Society 30 10 12011 1472011 211 Smith Kelle James Diabetic Association 31 350 1211020111 125/2011 054Ah Can Diabetic Association LD Luteing 12.2011 $100 12/6/2011 675 Diabetic Association LOG Jim 33 12/07/2010 $100 97 Ham Cary 12/10/2011 Diabetic Association Rogers Scott James 122/2011 360 Diabetic Association Smith James 35 215/2011 650Ah $60 2/19/2011 Carlin Diabetic Association Williams Jimmy 30 21012011 675 $100 2/01/2011 Tacott 3 Diabetic Association 2017/2011 007 $100 Gary 30 2/18/2011 2011 Diabetic Association Grand Total 29 Smi $50 James 2022/2011 30 Diabec ASOS 05011 Can $50 6/10/2011 40 2011 Diabetic Association 675 Ah Scott 5100 41 Diabetic Association 6/6/2011 17/2011 987 Haim Gary $100 42 10/2011 2011 DISEASON 20 Smith $60 Diabesc Association 2011 602/2011 90 AS Carin 580 44 2011 9/19/2011 Diabetic Acion $100 45 Scol 1772011 9/2011 Debele Association 987 Him 45 Gary Diabec Association 9/18/2011 366 mith 580 Habitat for Human 122/2011 5150 1212312011 Sort Donations Auto Filter Donations Donations Subtotalna 23 dage 509 Fisher th MAS T: 2011 $100 me Donations Summary Ready $80 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 86 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 12/27/2011 11/29/2011 2/13/2011 2/27/2011 1/29/2011 6/13/2011 6/27/2011 5/29/2011 9/13/2011 9/27/2011 8/29/2011 12/27/2011 12/14/2011 2/27/2011 2/14/2011 6/27/2011 6/14/2011 9/27/2011 9/14/2011 12/6/2011 2/6/2011 6/6/2011 9/9/2011 12/16/2011 12/2/2011 12/16/2011 2/16/2011 2/2/2011 2/16/2011 6/16/2011 6/2/2011 6/16/2017 9/19/2011 9/2/2011 9/19/2011 12/2/2011 12/20/2011 2/2/2011 2/20/2011 6/2/2011 6/20/2011 9/2/2011 9/20/2011 12/10/2011 2/10/2011 6/10/2011 9/10/2011 B D E F G 370 Wills Mary Habitat for Humanity $100 12/29/2011 432 Ash Jamie Habitat for Humanity $50 12/1/2011 366 Smith James Habitat for Humanity $150 2/23/2011 370 Wills Mary Habitat for Humanity $100 2/29/2011 432 Ash Jamie Habitat for Humanity $50 2/1/2011 366 Smith James Habitat for Humanity $150 6/23/2011 370 Wills Mary Habitat for Humanity $100 6/29/2011 432 Ash Jamie Habitat for Humanity $50 6/1/2011 399 Smith James Habitat for Humanity $180 9/23/2011 370 Wills Mary Habitat for Humanity $100 9/29/2011 432 Ash Jamie Habitat for Humanity $80 9/1/2011 365 Lee Sufeing Homeless Shelters $25 12/30/2011 977 Rogers Scott Homeless Shelters $80 12/19/2011 365 Lee Sufeing Homeless Shelters $25 2/30/2011 977 Rogers Scott Homeless Shelters $80 2/19/2011 365 Lee Sufeing Homeless Shelters $25 6/30/2011 977 Rogers Scott Homeless Shelters 580 6/19/2011 398 Lee Sufeing Homeless Shelters $28 9/30/2011 977 Rogers Scott Homeless Shelters 9/19/2011 345 Cross Roberta Leukemia Society $60 12/19/2011 345 Cross Roberta Leukemia Society $60 2/19/2011 345 Cross Roberta Leukemia Society $60 6/19/2011 348 Cross Roberta Leukemia Society $90 9/19/2011 234 Daniels Julie Make a Wish Foundation $225 12/29/2011 454 Cross Roberta Make a Wish Foundation $75 12/4/2011 867 Daniels Julie Make a Wish Foundation $130 12/21/2011 234 Daniels Julie Make a Wish Foundation $225 2/29/2011 454 Cross Roberta Make a Wish Foundation 2/4/2011 867 Daniels Julie Make a Wish Foundation $130 2/21/2011 234 Daniels Julie Make a Wish Foundation $225 6/29/2011 454 Cross Roberta Make a Wish Foundation $751 6/4/2011 867 Daniels Julie Make a Wish Foundation $130 6/21/2011 234 Daniels Julie Make a Wish Foundation $228 9/29/2011 484 Cross Roberta Make a Wish Foundation $78 9/4/2011 897 Daniels Julie Make a Wish Foundation $130 9/21/2011 543 Fisher Scott Meals on Wheels $125 12/26/2011 558 LOS Jim Meals on Wheels $115 12/22/2011 543 Fisher Scott Meals on Wheels $25 2/26/2011 558 LOS Jim Meals on Wheels $15 2/22/2011 543 Fisher Scott Meals on Wheels $65 6/26/2011 558 LOS Jim Meals on Wheels $55 6/22/2011 843 Fisher Scott Meals on Wheels $98 9/29/2011 888 Los Jim Meals on Wheels $88 334 Kellee 9/22/2011 Pete Police Federation $180 334 Kellee 12/14/2011 Pete Police Federation $180 334 Kellee 2/14/2011 Pete Police Federation $180 6/14/2011 334 Kellee Pete Police Federation $180 9/14/2011 Donations Summary Sort Donations Auto Filter Donations Donatio $75 Ready 94 95 96 97 98 12/3/2011 213/2011 6/3/2011 9/3/2011 945 Williams 945 Williams 945 Williams 948 Williams Jimmy Jimmy Jimmy Jimmy Vietnam Veterans Vietnam Veterans Vietnam Veterans Vietnam Veterans $60 $60 560 $90 12/13/2011 2/13/2011 6/13/2011 9/13/2011 4 Donations Summary Sort Donations Auto Filter Donations Dor Ready