Excel Chapter 11 Mid-Level 1 – Internships
As the Internship Director for a regional university, you created a list of students who are currently in this semester’s internship program. You have some final touches to complete the worksheet, particularly in formatting text. In addition, you want to create an advanced filter to copy a list of senior accounting students. Finally, you want to insert summary statistics and create an input area to look up a student by ID to display his or her name and major.
Start Excel. Download and open the file named Exp19_Excel_Ch11_ML1_Internships.xlsx. Grader has automatically added your last name to the beginning of the filename.
You want to extract the last four digits of the student’s ID.
In cell B2 on the Students sheet, extract the last four digits of the first student’s ID using the RIGHT function. Copy the function from cell B2 to the range B3:B42.
After extracting the last four digits of the ID, you want to align the data.
Apply center horizontal alignment to the range B2:B42.
The first and last names are combined in column C. You want to separate the names into two columns.
Convert the text in the range C2:C42 into two columns using a space as the delimiter.
You want to convert the text in column F to upper and lowercase letters.
Use a text function in cell G2 to convert the text in cell F2 into upper and lowercase letters. Copy the function to the range G3:G42.
Now that you have converted text from uppercase to upper and lowercase, you will hide the column containing the majors in all capital letters.
Hide column F.
You want to create a criteria range for the dataset.
Create a criteria range by copying the range A1:I1 and pasting it in cell A44. Create conditions
for Senior Accounting majors on row 45 and an OR condition for Junior Accounting majors in the respective cells on row 46
You are ready to perform the advanced filter.
Create an output range by copying the range A44:I44 to cell A48. Perform the advanced filter by copying data to the output range. Use the appropriate ranges for list range, criteria range, and output range
On the Info worksheet, you want to insert a database function based on conditions.
Display the Info worksheet and insert the DSUM function in cell B2 to calculate the total tuition for junior and senior accounting students. Use the range A1:I42 for the database, Tuition for the field, and the criteria range.
You want to insert database functions to perform calculations.
In cell B3, insert the DAVERAGE function to calculate the average GPA for junior and senior accounting students on the Students worksheet. Use mixed references in the ranges.
You want to identify the highest GPAs for junior and senior accounting majors.
In cell B4, insert the DMAX function to identify the highest GPA for junior and senior accounting students on the Students worksheet. Use mixed references in the ranges.
In cell B5, insert the DMIN function to identify the lowest GPA for junior and senior accounting students on the Students worksheet. Use mixed references in the ranges.
In cell B6, insert the DCOUNT function to count the number of junior and senior accounting
students on the Students worksheet. Use mixed references in the ranges.
In cell B9, insert the DGET function to retrieve the last name of the student who has the ID listed in cell A9. Use the column number representing the Last Name column for the field argument and use the criteria range A8:A9. Edit the function to make the column letters absolute. Copy the DGET function from cell B9 to cell C9. Edit the field number to represent the GPA column.
You want to format the results of the database functions.
Format the range B3:B6 with Comma Style. Decrease the number of decimal places to zero
for cell B6.
You want to identify the location of a particular ID.
Insert the MATCH function in cell B13 to identify the position of the ID stored in cell B12. Use
the range A2:A42 in the Students worksheet as the lookup_array argument and look for exact
Insert the INDEX function in cell B14 with Students!A$2:I$42 as the array, B$13 that contains
the MATCH function as the row number, and 4 as the column number. Copy the function from cell B14 to cell B15. Edit the function to change the column number to 7.
Change the ID in cell B12 to 11282378. The results of the MATCH and INDEX functions
You want to insert a function to display other functions as text.
Insert the FORMULATEXT function in cell D2 to display the formula that is stored in cell B2.
Copy the function to the range D3:D6 and to the range D13:D15. In cell D8, insert the
FORMULATEXT function to display the function that is stored in cell B9, and in cell D9, insert
the FORMULATEXT function to display the function that is stored in cell C9.
Increase the width of column D to 50.
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 on all sheets.
Save and close Exp19_Excel_Ch11_ML1_Internships.xlsx. Exit Excel. Submit the file as directed.
Why you should choose our essay writing company
Utahwriter is an essay writing company that provides students with the help they need to be successful in their academics. With a skilled team of writers, Utahwriter.com can provide you with top-quality content for any type of paper. We are so confident that our work will meet your needs, we offer a 100% satisfaction guarantee on all orders! If you're looking to get high grades and succeed in school, then don't hesitate to contact us today AND GET;
Amazing deals on our essay writing services
We know you're busy with school and other activities, so we want to make it easier for you by providing amazing deals on the essays that you need. We offer a wide range of discounts depending on how much work is required from us. Don't miss out on this opportunity to get some great grades - contact us today
Perfect papers every time
We are the best essay writing company because we ensure that every paper you receive is 100% original and free of any plagiarism. We have a strict anti-plagiarism policy to maintain our high standards, so rest assured knowing that your content will be unique when you order from Utahwriter.com!
24/7 client support
Our clients come first! That's why we have a 24/7 customer support system in place. We know that you may need some help during the academic process, so if you have any questions or concerns about your order - don't hesitate to ask! Whether it be by email, phone call, live chat with one of our representatives today.
Try it now!
How it works?
Place your order
You can order an essay with us by filling out our quick and easy order form.
Proceed with the payment
After receiving payment confirmation via PayPal or credit card – we begin working on your detailed outline, which is based on the requirements given by yourself upon ordering. All work goes through three levels of quality assurance before being sent back to you for review.
Receive the final file
Once approved, your order is complete and will be emailed directly to the email address provided before payment was made!
Benefits of getting that essay from us!
Our well written essays are what professors and tutors are looking for. Our writers are experienced and they know what is expected in every essay. We guarantee you that high score in every essay.
100% original content
We are the best essay writing company because we ensure that every paper you receive is 100% original and free of any plagiarism. We have a strict anti-plagiarism policy to maintain our high standards, so rest assured knowing that your content will be unique when you order from Utahwriter.com
Money back guarantee
We know that you might be worried about the quality of our work, but don't worry! We offer a 100% money back guarantee on all orders. If for any reason you are unsatisfied with anything relating to your order - please contact us within 14 days and we will provide a full refund no questions asked. .
Professional writers only work on your essays!
Our essay writers are not only experienced, but they also have degrees in their field of study. Our experts will write you a custom paper that is tailored to your needs and expectations!
We know how busy students can be with schoolwork and extra-curricular activities. That's why we work 24/7 so you can get your paper done on time no matter how short of a deadline you have!
Quick response to any questions or concerns
Our writers are professionals and they know that it is important to keep the client updated with every step of their order. We offer quick responses along with all the information you need to know about your essay.