Open Language School.xlsx and save it with a new name that includes your name.
computer science
Description
Part A:
Open Language School.xlsx and save it with a new name that includes your name. Mountainview Language School in Denver, CO, is a small school that provides language training in English, Spanish, and French to students from all over the world. The school keeps track of its student records in Excel. You’ve been asked to work with the current spreadsheet and improve it so that information and issues can be analyzed and tracked. You use advanced IF functions and a lookup table to calculate data related to student payments, and then you use the COUNTIF, SUMIF, and AVERAGEIF functions to analyze the payment data.
1. Create an Excel table and give it an appropriate name.
2. In cell K4, add a new column called Owing.
3. Freeze the rows from the column headings up to Row 1.
4. In cell K5, enter an IF function that enters Owing in cell K5 if the Balance is greater than 0 and Up to Date if the value is 0 or less.
5. Students who are enrolled in a Level 1 course pay $2,100 in tuition, and students enrolled in Levels 2, 3, and 4 courses pay $3,300 in tuition. In cell F5 (Tuition), enter a function to enter the correct tuition fee.
6. A 15% discount is offered to students who are taking Level 1 English. In cell I5, enter a function to calculate the discount amount. If neither criterion is met, a 0 should show.
7. In the Lookup worksheet, create a Lookup table to reflect the material costs for each program. Include clear labels. The material costs are as follows: $540 for English, $392 for Spanish and $415 for French.
8. In cell G5 of the Student Records worksheet, use VLOOKUP or HLOOKUP to enter the appropriate material costs based on the program a student is taking.
9. In cell J5 (Balance), enter the formula that calculates the balance based on the values in the Tuition, Material Costs, Paid and Discount columns.
10.Add a total row and choose functions which would be meaningful tallies.
11.Use conditional formatting to highlight any duplicate values in the Student ID column. For
the formatting, select black text and a light red fill color.
