Assignment #5
Download the workbook
Assignment 5
Create a Name(s) worksheet, and enter the names of the students
working together on this assignment in cells A1 and A2
(boldface the names).
If Excel shows a Security Warning: "Macros have been disabled"
click the Options... icon and then click the "Enable this content" radio
button and click OK.
Note that the cell formats are correct on each worksheet; you don't have to
change them (although you will change some conditional formatting on some
cells).
Also, you need to enter the correct formulas in the cells;
just entering the right values in these cells is pointless -meaning
you won't get any points for doing so.
Fill in the worksheet named Secretary+ as follows:
- Green cell B1 specifies the # of "pre" interviews to do before
actually interviewing candidates to hire (just to get a rank
threshold).
We can enter any values from 1 to 99 in this cell.
- Enter a formula in cell D2 that calculates the number of
interviews needed to hire a person after the pre-interviews
specified in cell B1.
Hint: Cell B3 calculates the rank of the person selected.
Use the MATCH function to find the index where this rank occurs
in column C (which corresponds to the total number of
people interviewed) and subtract the number of people pre-interviewed.
(To determine if your formula is correct, examine the value in this cell
with a small # "pre" interviews in cell B1).
Remember that pressing F9 causes a recalculation.
-
Enter the Data Table covering columns F and G:
F calculates the rank of the person hired for each hiring
experiment and G calculates the number of interviews after the
"pre" interviews (calculated by the formula you just wrote in cell
D2).
These columns don't really depend on the Experiment # (1, 2, ... 500),
so when prompted by the Data Table function, put the values from column
E into cell D1, which is empty and doesn't affect the
calculation.
-
Enter a formula in cell D3 that calculates the average of all the
data collected in column G of the Data Table.
We will use the information on this worksheet in the next Lab Assignment
to plot these averages for different # "pre" interviews.
Fill in the worksheet named Chart as follows:
- Green cells B1:C7 specify information about where to find the
data on this worksheet for graphing.
The relevant rows and columns both appear as numbers (i.e., columns
do not appear as letters).
Do not change information in these cells.
To speed up Excel while on this page, first click the Formulas
tab, then click the click the Calculation Options icon, and
finaly click "Automatic Except for Data Tables".
By doing so, Excel will not recalculate the Data Table on the
Secretary+ worksheet.
Because no more problems involve Data Tables, you can leave this
calculation in effect for the rest of the assignment; but before
submitting it, change this option to Automatic
- Create a scroller in cells C9:D10 that links its raw value
with cell B9; it should generate raw values between 1 and 13.
- Enter a formula in cell B14 that calculates (according to the
current value in cell B9) the number of points for the problem
being selected and graphed.
The number of points for each problem is stored in different columns in
row 43 (it always appears 2 rows above where the First Student
Scores appear; see cell B4 which contains 45).
Use the ADDRESS function to calculate a text representation of the
cell (row and column) in
which the number of points is stored (using cells B4,
B6, and
B9) and then use the INDIRECT function with this text
to calculate the numeric value.
When the problem selected is 1 the maximum number of points is
12; when the problem selected is 2 the maximum number of
points is 10, etc.
Alternatively, use the OFFSET function to calculate this value.
- Enter a formula in cell A11 that calculates (according to cell
B9) the text description of which problem is being selected
and graphed; use the values in cells
B2 and B3 to build the text "A17:A29", then use
the INDIRECT function to convert this text to a range that the
INDEX function uses (with the value in cell B9) to
choose the correct text description of the problem.
Append two spaces to the front and more text to the rear that describes
how many points the selected problem is worth (see the value in cell
B14, whose specification is described above).
For the first problem, it should show
" 1. Linked List: Points 0 - 12" but without the quotation
marks.
Alternatively, use the OFFSET function to calculate this value.
- Enter a formula in cell B12 that calculates (according to cells
B6 and B9) which column to graph.
This is just a numeric formula, yielding the column number (column
B would be column 2, C would be column 3, etc.)
- Enter a formula in cell B13 that calculates (according to cells
B4, B5, and B12) text that represents the range
of cells to graph: use two calls to the ADDRESS function and
catenate a ":" in between the values returned by these two functions.
Show the text as a relative range (because it fits more compactly in
the cell).
For the first problem, it should show B45:B116.
- Move the scroller and ensure cells B9, A11, and
B12:B14 change correctly, before going on to the next part.
- Enter formulas in cells Q45:Q116 that calculate the student's
score for the problem selected to graph (based on the value in cell
B9).
There are many ways to do this; one simple way is to use the INDEX
function on one student's row of scores, indexing the needed column.
That is, for row 45, the score that appears in cell Q45 is the
one from row 45, whose column index is specified by cell B9;
the same for rows 46 through 116.
The histogram in cells R45:S80 will update automatically,
according to whatever values are in Q45:Q116; if you get these
values correct, the historgram/graph will be correct.
- Move the scroller and ensure the chart is updated appropriately,
including the legend and graphed columns, which change as the problem
changes.
- Conditionally format all the cells in cells B45:N116 so that any
student score which is the same as the points for that problem is
filled in blue.
Hint: You can put the same condition, if chosen carefully (think
a mixed reference), in all cells that are scores.
Fill in the worksheet named Random+Sort as follows:
- Enter formulas in cells B2 to B18 that calculate
random numbers in the standard range [0,1).
- Enter formulas in cells C2 to C18 that calculate
the rank of these random numbers: the highest number should
have the lowest rank: use the RANK function.
- Enter formulas in cells D2 to D18 that use the INDEX
function to calculate the name matching the calculated rank.
That is, if cell C2 has the rank 10, then the name Jack
(the 10th one in column A; the first name in that column is
Alice) should appear in cell D2.
- So, column D is a random permutation of the names in column
A: pressing F9 (or the Recalculate icon) will generate
new random numbers, new ranks, and a new permutation of the names.
- Enter formulas in cells C26 to C42 that calculate
the rank of the students' scores in column B: use the
RANK, specifying a third argument that is in the green cell
B21 (which should store 0 or 1; wee the text in C21)).
- Enter formulas in cells D26 to D42 that use the
INDEX and MATCH functions to calculate the name whose
rank matches the required index (column G).
That is, cell D26 should store the name of the student whose score
rank is 1 (the 1 comes from cell G26); cell D27 should
store the name of the student whose score rank is 2 (the 2 comes from
cell G27); etc.
- Enter formulas in cells E26 to E42 that use the
INDEX and MATCH functions to calculate the score for
name to its left (the same score to the right of the name in column
A).
These scores should be in decreasing or increase order (depending on
whether cell B21 stores a 0 or 1).
- Thus, columns D and E show every student and his/her
score, sorted by the scores.
We can change the green cell B21 back and forth between 0 and 1:
the cells in column D and E should sort themselves back
and forth between largest to smallest score and smallest to largest
score.
We can also change any of the scores in the green cells in column
B and watch that student and his/her new score change position
in sorted columns D and E (e.g., change Paul's
score to 99).
Fill in the worksheet named Prices as follows:
- Green cells B1:B3 specify an order: a type of shirt, a size, and a
quantity.
We can enter different values in these cells.
Also, cells E3:I8 specify the standard costs of each shirt type
according to its size; and cells D13:F19 specify the discount
percent (off the standard price of each shirt) for the quantity ordered.
- Enter a formula in cell B4 that calculates the row index of the
specified shirt (it must be exact): use the MATCH function.
For example, Camp's row index is 1, ..., Rugby's row index
is 6.
- Enter a formula in cell B5 that calculates the column index of the
specified size (it must be exact): use the MATCH function.
For example, 30's column index is 1, ..., 50's column index
is 5.
- Enter a formula in cell B6 that calculates the standard cost of
the shirt type and its size: use the INDEX function, and the row
and column indexes you computed in cells B4 and B5 above.
For example, a Pinpoint shirt of size 35 has a standard
price of 36.00.
- Enter a formula in cell B7 that calculates the discount for the
quantity of shirts: use the LOOKUP function, looking up
information in column D and producing a result from column
F.
For example, a quantity of 22 has a discount percentage of
.05.
- Enter a formula in cell B9 that calculates the cost of buying the
shirts, including the discount: use a mathematical formula.
For example, a Pinpoint shirt of size 35 ordered in a
quantity of 22 has a total cost (including the discount) of
752.40.
- Add conditional formating to cells E3:I8 so that it highlights in
orange the cell specified by B4 and B5.
Hint: in the conditional formating, select all these cells and
then "Use a formula to determine which cells to format"; the function
ROW() with no arguments in the ()s calculates the row of the
cell that the formula appears in (same for COLUMNS).
Compare these values to cells B4 and B5, doing a bit
of "fixup" arithmetic to get the right answers.
- Add conditional formating to cells F13:F19 so that it highlights in
orange the cell specified by B7.
- Add conditional formating to cells D13:E19 so that it highlights
in orange the cells determining the discount specified by B7.
Hint: in the conditional formating, select all these cells and
then "Use a formula to determine which cells to format"; the function
ROW() with no arguments in the ()s calculates the row of the
cell that the formula appears in; also MATCH gives an index
for the Discount percent in column F.
Compare these values, doing a bit of "fixup" arithmetic to get the right
answers.
- Caution: Ensure the conditional formating formulas are not
surrounded by quotes.
Examine the
Assignment 5 Solution pdf
to see how the results should appear in your workbook.
If Excel shows a Security Warning: "Some active content has been disabled"
click the Options... icon and then click the "Enable this content" radio
buton and click OK.
Remember: before submitting this worksheet, change the Calculation
Option to Automatic.