Gareth Glynn said:
The use of relative and absolute cell references in formulae seems profound and powerful; I guess full value will become evident in practice
Calculate the Cost of Each of Johnson's Widgets' Product
Learn how to use absolute and relative references.
For the rest of this course, you will be working towards answering a simple question: What was Johnson's Widgets' profit for every month in 2011?
To begin answering this question, we need to first determine the cost of each sale made, so that we can compare those to the revenue. To begin, head over to the second sheet of the spreadsheet, Costs. The manufacturing department has provided you with these figures: for each of the three products the company sells, you are given its materials cost, its weight, and the number of hours it takes to make.
First,
First, let's figure out the shipping cost of each product, in column F. To calculate the shipping cost of the first product, highlight cell F8 and enter the formula
=C8*C4
and press enter. Here, you are simply multiplying the weight of the product (from cell C8) by the per-kg shipping cost (from cell C4). It's similar to how you calculated total revenues in the last task. Now, copy this formula into the remaining two shipping cost cells.
Can you figure out why this didn't work? It's because when we copy and paste formulas, the referenced cells change so as to remain relative to where the formula is written. This is what we want for the first half of the formula: we want to use the weight of each individual product, but by moving the reference in the second half of the formula down, we're no longer multiplying by the shipping costs. To remedy this, we use the dollar-sign symbol to create an absolute (as opposed to relative) reference to the shipping cost. In cell F8, insert a "$" before both the (second) C and the 4, so that the formula reads
=C8*$C$4
Now you can copy this new formula to the two remaining cells, with the desired result.
The labor cost of each product is analogous. See if you can write the formula for column G to multiply the labor hours by the hourly labor cost.
Finally, calculate the total labor cost for each product by adding together the Materials Cost, the Shipping Cost, and the Labor Cost. You can do this either by using the "+" sign, or by using the SUM formula.
A few notes on absolute and relative references:
- Putting the dollar signs before both the column and the row reference locks the reference by both column and row. There are some cases where you may want to lock just one, which you can do by only using one dollar sign, e.g. "$C4" or "C$4".
- Instead of typing the dollar signs, you can put the cursor in the reference to the cell you'd like to lock, and press the F4 key to cycle through the four types of references.