An Advanced Filter

Project Description:You work for the vice president’s office at a major university. Human Resources provided a list of deans and associate deans, the colleges or schools the represent, and other details. You will use text functions to manipulate text, apply an advanced filter to display selected records, insert database summary statistics, use lookup functions, and display formulas as text.Start Excel. Download and open the file named Exp19_Excel_Ch11_CapAssessment_Deans.xlsx. Grader has automatically added your last name to the beginning of the filename.First, you want to combine the year and number to create a unique ID.In cell C8, enter 2006-435 and use Flash Fill to complete the IDs for all the deans and associate deans.Next, you want to create a three-character abbreviation for the college namesn cell E8, use the text function to display the first three characters of the college name stored in the previous column. Copy the function to the range E9:E28.The college names are hard to read in all capital letters.In cell F8, insert the correct text function to display the college name in upper- and lowercase letters. Copy the function to the range F9:F28.You want to display the names in this format Last, First.In cell J8, insert either the CONCAT or TEXTJOIN function to combine the last name, comma and space, and the first name. Copy the function to the range J9:J28.Columns K and L combine the office building number and room with the office phone extension. You want to separate the office extension.Select the range K8:K28 and convert the text to columns, separating the data at commasYou decide to create a criteria area to perform an advanced filter soon.Copy the range A7:M7 and paste it starting in cell A30. Enter the criterion Associate Dean in the appropriate cell on row 31Now you are ready to perform the advanced filter.Perform an advanced filter using the range A7:M28 as the data source, the criteria range you just created, and copying the records to the output area A34:M34.The top-right section of the worksheet contains a summary area. You will insert database functions to provide summary details about the Associate Deans.In cell L2, insert the database function to calculate the average salary for Associate DeansIn cell L3, insert the database function to display the lowest salary for Associate Deans.In cell L4, insert the database function to display the highest salary for Associate Deans.Finally, you want to calculate the total salaries for Associate Deans.In cell L5, insert the database function to calculate the total salary for Associate Deans.Format the range L2:L5 with Accounting Number Format with zero decimal places.The range G1:H5 is designed to be able to enter an ID to look up that person’s last name and salary.In cell H3, insert the MATCH function to look up the ID stored in cell H2, compare it to the IDs in the range C8:C28, and return the position number.Now that you have identified the location of the ID, you can identify the person’s last name and salary.In cell H4, insert the INDEX function. Use the position number stored in cell H3, the range C8:M28 for the array, and the correct column number within the range. Use mixed references to keep the row numbers from changing. Copy the function to cell H5 but preserve formatting. In cell H5, edit the column number to display the salary.In cell D2, insert the function to display the formula stored in cell F8.In cell D3, insert the function to display the formula stored in cell H3In cell D4, insert the function to display the formula stored in cell H4.In cell D5, insert the function to display the formula stored in cell L3.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.Save and close Exp19_Excel_Ch11_CapAssessment_Deans.xlsx. Exit Excel. Submit the file as directed.For more information on An Advanced Filter check on:https://en.wikipedia.org/wiki/Great_Filter 

Don't use plagiarized sources. Get Your Custom Essay on
An Advanced Filter
Just from $13/Page
Order Essay
Acme Writers
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

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.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Reviews

Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Reviews

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.