- 20088 Average Donation 1 (96.38 KiB) Viewed 40 times
Start Excel. Download and open the file named
Exp19_Excel_Ch11_Cap_Donors.xlsx. Grader has automatically
added your last name to the beginning of the filename.
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 the database function to calculate the
average donation for the records that meet the conditions in the
criteria range.
15
In cell K4, insert the database function to count the number of
records that meet the conditions in the criteria range.
16
Format the range K2:K3 with Accounting Number Format with zero
decimal places. Format cell K4 with Comma Style with zero decimal
places.
17
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.
18
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.
19
Save and close Exp19_Excel_Ch11_Cap_Donors.xlsx. Exit
Excel. Submit the file as directed.
20088*** Average donation