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.
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.
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.
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.
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.
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 .
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.
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.
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.
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.
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.
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.
In cell K3, insert the database function to calculate the average donation for the records that meet the conditions in the criteria range.
In cell K4, insert the database function to count the number of records that meet the conditions in the criteria range.
Format the range K2:K3 with Accounting Number Format with zero decimal places. Format cell K4 with Comma Style with zero decimal places.
You want to use the FORMULATEXT function to display the functions.
In cell G2, insert the FORMULATEXT function to display the formula stored in cell B3.
In cell G3, insert the FORMULATEXT function to display the formula stored in cell B4.
In cell G4, insert the FORMULATEXT function to display the formula stored in cell D8.
In cell G5, insert the FORMULATEXT function to display the formula stored in cell K2.
Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of the worksheet.
Save and close Exp19_Excel_Ch11_Cap_Donors.xlsx. Exit Excel. Submit the file as directed.
Why Choose Us
We value our clients. For this reason, we ensure that each paper is written carefully as per the instructions provided by the client. Our editing team also checks all the papers to ensure that they have been completed as per the expectations.
Professional Academic Writers
Over the years, our Acme Homework has managed to secure the most qualified, reliable and experienced team of writers. The company has also ensured continued training and development of the team members to ensure that it keep up with the rising Academic Trends.
Our prices are fairly priced in such a way that ensures affordability. Additionally, you can get a free price quotation by clicking on the "Place Order" button.
We pay strict attention on deadlines. For this reason, we ensure that all papers are submitted earlier, even before the deadline indicated by the customer. For this reason, the client can go through the work and review everything.
At Essay Writing Help, all papers are plagiarism-free as they are written from scratch. We have taken strict measures to ensure that there is no similarity on all papers and that citations are included as per the standards set.
Customer Support 24/7
Our support team is readily available to provide any guidance/help on our platform at any time of the day/night. Feel free to contact us via the Chat window or support email: firstname.lastname@example.org.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
Essay Writing Help has stood as the world’s leading custom essay writing services providers. Once you enter all the details in the order form under the place order button, the rest is up to us.
At Essay Writing Help, we prioritize on all aspects that bring about a good grade such as impeccable grammar, proper structure, zero-plagiarism and conformance to guidelines. Our experienced team of writers will help you completed your essays and other assignments.
Admission and Business Papers
Be assured that you’ll definitely get accepted to the Master’s level program at any university once you enter all the details in the order form. We won’t leave you here; we will also help you secure a good position in your aspired workplace by creating an outstanding resume or portfolio once you place an order.
Editing and Proofreading
Our skilled editing and writing team will help you restructure you paper, paraphrase, correct grammar and replace plagiarized sections on your paper just on time. The service is geared toward eliminating any mistakes and rather enhancing better quality.
We have writers in almost all fields including the most technical fields. You don’t have to worry about the complexity of your paper. Simply enter as much details as possible in the place order section.