Donors Look Up Area Formulas Summary Donor ID Art 39 Cell B3

Donorslook Up Areaformulassummarydonor Idart 39cell B3total Donations

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. 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.

Sample Paper For Above instruction

Title: Managing Donor Data with Advanced Excel Functions for University Development

Effective management of donor information is vital for university development offices aiming to foster relationships and enhance fundraising efforts. In the context of leveraging Excel for such tasks, the process involves data cleaning, categorization, lookup functions, advanced filtering, and reporting. This paper illustrates the methodological approach to organize, analyze, and summarize donor data efficiently by applying a suite of Excel functions and features, centering on a university’s donor portfolio file.

Initially, the researcher begins by importing and preparing the data. The provided spreadsheet includes columns such as college or school name, donor ID, full name, address, city, state, and donation amount. To facilitate better analysis, extracting a standardized code from the college name is essential. Using the LEFT function in cell B8, the first three characters of the college name are retrieved, providing a concise college identifier. Copying this formula down the column ensures uniform coding across all records, which simplifies subsequent data operations. Combining this code with donor IDs forms a unique identifier, achieved via the CONCAT function. In cell D8, the concatenation of the college code and donor ID, separated by a hyphen, creates a composite ID, enhancing record uniqueness and traceability.

Next, for improved readability, the college names are re-formatted with only the first letter capitalized using the PROPER function in cell J8, which is then replicated down the column. The full name of donors, initially displayed as 'Last, First,' is to be split into only last names for targeted analysis. Employing the Flash Fill feature, the last names are extracted into column F, which expedites the process without complex formulas.

Address data stored as combined text strings are separated into street address, city, and state components via the Text to Columns feature. Selecting the address range and choosing comma as the delimiter splits the information into manageable parts, improving data accessibility and reporting.

Furthermore, the spreadsheet supports dynamic donor lookup by donor ID. Using the MATCH function in cell B3, the position of a donor in the list is identified relative to the entered donor ID. This positional index then feeds into the INDEX function, which retrieves specific information such as full name and donation amount—these are presented in designated cells for quick reference. The donation value is formatted as currency with zero decimal places to ensure clarity.

Advanced filtering mechanisms are employed to isolate donor records based on specific criteria, such as donors in the College of Business who donated at least $1,000. Copying header rows to define criteria ranges, and then setting conditions like 'Business' and '>=1000' facilitates targeted analysis. The Advanced Filter feature copies the matching records into a separate output range for detailed examination.

To summarize the filtered data, database functions are integrated. The SUM, AVERAGE, and COUNT functions compute total donations, average donations, and donor counts, respectively, for the selected subset. These are entered in designated summary cells and formatted appropriately to enhance readability.

Additionally, the FORMULATEXT function is used to display the underlying formulas for key calculations, promoting transparency and auditability of the process. Lastly, a custom footer with user identification, sheet, and file information is added for documentation purposes.

By systematically applying these Excel features, the university development officer can efficiently manage and analyze donor data, supporting strategic fundraising initiatives. The approach exemplifies how combining text functions, lookup formulas, filtering, and summary statistics forms a comprehensive toolkit for effective donor data management in Excel.

References

  • Walkenbach, J. (2018). Excel 2019 Bible. Wiley.
  • Gwartney, D., & Terrill, R. (2019). Using Excel Data Analysis Functions. Journal of Data Management, 34(2), 45-53.
  • Olsen, D. (2020). Mastering Excel for Data Analysis. Tech Publishing.
  • Schaeffer, P. (2017). Advanced Excel Techniques. McGraw-Hill Education.
  • Chou, T. (2019). Excel for Business and Data Analysis. Pearson.
  • Microsoft Support. (2023). Using Text to Columns. https://support.microsoft.com
  • Shannon, S. (2021). Data Management in Education Institutions. Educational Technology Journal, 29(4), 123-130.
  • Healy, D. (2022). Practical Excel for Data Analysis. O'Reilly Media.
  • Johnson, M. (2020). Effective Use of Excel Functions. Journal of Business Analytics, 12(3), 66-72.
  • Microsoft Excel Documentation. (2023). FORMULATEXT Function. https://support.microsoft.com

Related Assignments