Applications Programming --- Lab 2
Objective:
- Get familiar with Excel
- Get familiar with Excel formula, especially with cell reference
and some basic built-in functions
References:
Problem Description
An elementary school let its classroom teachers help to collect
payment for the supplies needed by the class students.
In the input file of Lab 2,
a list of supplies and their respective price is stored
in worksheet named "Information" in the range of A1 to B11.
And in the same worksheet, in the range of D2 to F9,
each row shows a class
with its classroom's teacher's name, its grade and the number
of students in this class.
In the worksheet named "Orders", there are two tables showing
two order lists. The first table is for orders of the supplies
that each student in each class must have. Row 2 shows
these must have item's product name. And row 3 shows the number
of the supplies needed by each student. Column A of this
table shows the classroom teachers' names.
The second table in the "Orders" worksheet shows a list orders
of extra supplies made by students. Each row stores an order.
Each order shows the name of the student who made this order,
the name of the student's classroom teacher, the name of
the product ordered, and the number of the product ordered.
Note that the data shown in the input file is only a sample
collection to show you the format of the data.
You should assume that there are way more data in these two
worksheets, especially in the Optional Order list.
Your tasks
- Download the Lab 2's input workbook:
lab2-input.xlsx
- In the "Must Have Items" table,
enter an Excel formula to cell B5 in "Orders" worksheet to
find the number of students in the corresponding teacher's
class. This formula should be readily re-usable without
any manual modification to cells from B6 to B12.
- Enter an Excel formula to cell C4 in "Orders" worksheet to
find the unit price of the corresponding product.
Again, this formula should be readily re-usable without
any manual modification to cells from D4 to F4
- Enter an Excel formula to cell C5 in "Orders" worksheet
to calculate how much
the teacher (in cell A5) should collect for the product
(in cell C2). The amount should be calculated as
the number of students in this teacher's class (B5) times
the number of the products needed by each student (C3),
then times the unit price of the product (C4).
This formula should be readily re-usable without
any manual modification to cells from C5 to F12.
- In cell H5, enter an Excel formula
to calculate how much the teacher (A5) needs to collect
for all the must have items in her/his classroom.
The calculation is done by sum the values in the range
from C5 to F5. Copy and paste the formula to range from
H6 to H12 to do the same calculation for every teacher.
- In the "Optional Orders" table in "Orders" worksheet,
enter an Excel formula in cell E17 to find the unit price
of the product ordered, and enter an Excel formula in Cell F17
to calculate the charge as unit price (E17) times number of items
(D17). These two formula should be re-usable in the corresponding
cells in the rest of the orders in this table.
- Add a new worksheet named "Optional Order Summary".
In this newly added worksheet, insert a pivot table.
The pibot table should show, for each classroom teacher,
the total number of orders
made in his/her classroom and the total charges the teacher
should collect for these orders.
- Save your work.
In your formula, if possible, you MUST always use cell references
instead of literal values. Pay attention to
the cell reference type, so that copy/pasted formula always give
you the right result.
There is a weekly assignment 2
following this lab.