You are a development officer for a state university. As an officer, you manage a portfolio of important donors who contribute financially to different areas within the university. You categorize the donors based on the college or school for which they want their donations associated. You recently downloaded the portfolio to an Excel workbook. Based on the way the data downloads from the main database, you want to format the text for readability and to make it easier for you to analyze. In addition, you will create an advanced filter to review a list of donors for a particular college or school. Finally, you want to create a look up area to look up data for a specific donor and create a summary section. 2 The first column displays the   name of the college or school (such as ART or BUSINESS ) associated with each.   You want to assign a three-character code for each college and use that code   to attach to existing donor IDs to create a unique field. In cell B8, insert the LEFT function to extract the first three characters   from the college name in cell A8. Copy the function to the range B9:B35. 3 You now want to combine the college   ID and donor ID. In cell D8, insert the CONCAT function to combine the college ID in cell B8   with the donor ID in cell C8 with a hyphen between the two text strings. Copy   the function to the range D9:D35. 4 In cell J8, insert a text   function that displays the college name from cell A8 with just the first   letter capitalized, such as Engineering .   Copy the function to the range J9:J35. 5 The Full Name column displays   last and first names of the donors. You want to display last names only in a   separate column. In cell F8, type Schneider and use Flash Fill to fill in the last names for   the donors in the range F9:F35. 6 The Address column contains   street addresses, city names, and state abbreviations. To manage the address   list better, you will separate the data into three columns. Select the addresses in the range G8:G35 and convert the text to columns,   separating the data at commas . 7 The top-left section of the   spreadsheet is designed to be able to enter a donor’s ID, such as ENG-15, and   look up that person’s position in the list, display the donor’s full name,   and display the amount donated this year. The first step is to identify the   position number of the donor ID. In cell B3, insert the MATCH function to look up the donor ID in cell B2,   compare it to the list in the range D8:D35, and then return the donor’s   position within the list. 8 Now you are ready to use the   position number as an argument within the INDEX function. 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 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 You want to format the results   of the INDEX function. Format the value in cell B5 as Accounting Number Format with zero decimal   places. 11 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 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. 13 Now that you created a copy of   the records meeting the conditions, you are ready to enter database functions   in the Summary area. 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 In cell K3, insert …

Looking for solution of this Assignment?


We deliver quality original papers

Our experts write quality original papers using academic databases.We dont use AI in our work. We refund your money if AI is detected  

Free revisions

We offer our clients multiple free revisions just to ensure you get what you want.

Discounted prices

All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount

100% originality

We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity.We have ZERO TOLERANCE TO USE OF AI

On-time delivery

We will deliver your paper on time even on short notice or  short deadline, overnight essay or even an urgent essay