Chapter 3. References: Point in the right direction
A formula is only as good as its references.
No matter how creative and brilliant your formula is, it won’t do you much good if it does not point to the correct data. It’s easy to get references right for short, individual formulas, but once those formulas get long and need to be copied, the chance of reference mistakes increases dramatically. In this chapter, you’ll exploit absolute and relative references as well as Excel’s advanced new structured reference feature, ensuring that no matter how big and numerous your references are, your formulas will stay tight and accurate.
Your computer business is in disarray
As the boss of Ace Computer Manufacturing, you know how critical it is to maintain your profit margins in the volatile and competitive business of selling computers.
You need to get on top of your supply chain: are you marking up your computers enough to make a profit? You need to take control of your data to figure it out.
Here are last quarter’s costs and sales for low-end computers. The figures below list the cost and revenue for a single computer. Calculate the profit margin on this configuration. How profitable is a single sale of this model?
To calculate the Total cost, add the cost of the components.
To calculate the Gross profit margin, subtract the total cost by the sale price and divide the whole thing by the sale price.
Gross profit margin is equal to:
Your production manager has a spreadsheet with costs
In the computer industry, component prices are always changing, so in order to ensure that you’re getting a healthy margin on the computers you sell, you need to make sure you’re getting the best deal you can.
If you bought the least expensive acceptable parts, how much would the resulting configuration cost?
Load this!
www.headfirstlabs.com/books/hfexcel/hfe_ch03_low_end_forecast.xlsx
There are multiple worksheets in this file.
You need a function to find the lowest price on the memory worksheet.
MIN returns the lowest number in a series
When you have a range of numbers and want to figure out what is the smallest number in that range, use the MIN function.
And, in case you hadn’t guessed it already, the function that tells you the largest number in a list is MAX.
What you need to do now is write MIN formulas to find the lowest cost for each of these components and see how much your low-end computer configuration will cost this quarter. Using that information, you’ll be able to forecast your profitability.
Let’s start by looking at memory. We need a formula that will return the lowest amount we can pay for memory.
If you write your formula the way you’ve been writing formulas, how will your formula know which sheet to refer to for the data?
____________________________________________________
____________________________________________________
____________________________________________________
____________________________________________________
How would you design a reference to ensure that the correct sheet is being pointed to?
____________________________________________________
____________________________________________________
____________________________________________________
____________________________________________________
You just grappled with the question of how to refer to cells across worksheets. What did you conclude?
If you write your formula the way you’ve been writing formulas, how will your formula know which sheet to refer to for the data?
The formula wouldn’t know where to find the data. The range we want is
D4:D19, but it has to be on the LowMem sheet. We don’t want data from that range on other sheets.How would you design a reference to ensure that the correct sheet is being pointed to?
The reference would definitely have to have the name of the worksheet baked into it. Something like this:
LowMem-D4:D19.____________________________________________________
Let’s see if Excel can help figure out these ranges....
Let Excel fill in ranges by starting your formula and using your mouse
Excel does indeed have a syntax for pointing to ranges that exist on worksheets other than the worksheet where your function resides. And you’re about to learn that syntax. But not from this book.
Instead, Excel’s going to show you. One handy trick in writing formulas with arguments that involve cell ranges is to start writing a formula, getting to the argument of your function where you want to put the range but not typing anything:
Now, instead of typing the reference, just use your mouse to select the data you want. Go ahead and click on a different worksheet (or even a different workbook) and select your data with the mouse. Excel will fill in the range for you.
Finally, type a comma for your next argument or a ) symbol to end your formula and press Enter.
What happens?
Excel got the right answer using a more sophisticated reference
When you use the mouse to select the data you want your MIN formula to evaluate, Excel automatically fills in the range for that data, even if the data is on a different worksheet.
Here’s how Excel describes the range for memory prices you want to evaluate.
The range does look a little similar to the first one you tried...
...except that in this case it adds the LowMem! element, which tells Excel to evaluate the range D4:D19 on the LowMem worksheet. So far, we haven’t included an element to describe which worksheet we want to reference, and Excel has taken that to mean that we want to reference the same worksheet where we’ve put our formula.
Now filling in the lowest prices for the rest of the components will be a snap!
You know everything you need to know to complete the rest of this spreadsheet and forecast the profitability of low-end computers for next quarter.
Using the
MINfunction and pointing to the hard disk (LowHDs) and motherboard (LowBds) pricing worksheets, calculate what is the lowest price you can get for these components.Type in the formula to calculate your new cost total in
B9.Your sales team has determined that the highest competitive price you can charge your customers is $390. Fill that value into cell
B11.Calculate your new gross profit margin.
You just projected your profitability for low-end computers, given your component costs, for the next quarter. Do you expect a profit?
Geek Bits
A quick way to select all the numbers in column D for your MIN formula: you can type MIN(LowBds!D:D). If there are other elements in the column, such as text or blank spaces, Excel will just ignore them.
Nice work.
Looks like you’ll have a handy profit per unit this quarter. Now all we have to do is sell them....
Things just got even better...
Sweet! That definitely means that the computers you sell will earn a higher profit margin. Better calculate to see how much.
Use absolute references to prevent shifting on copy/paste
You’ve thus far been using relative references in your formulas...
...and when you copy and paste them, relative references shift in proportion to the original formula.
But sometimes you want a reference to stay fixed no matter where you copy and paste it. In that case, you need to use an absolute reference.
To tell Excel to make a reference absolute, the syntax is to add dollar signs. If you put a dollar sign before the column, the row can shift, and if you put one before the row, the column can shift, and if you use two dollar signs, the reference will stay totally fixed.
Your profit margin is now even higher...
With your corrected formula using absolute references, you were able to copy and paste to your motherboard and hard disk fields, showing your discounted component costs.
Copying cell C8 and pasting to cell C11 would normally shift the reference in that cell from B4 to B7—a shift in rows. To prevent the row from shifting, put a $ in front of the 4 in your reference.
You can also put a $ in front of the B in your reference, but it won’t make a difference, because copying cell C8 to cell C11 wouldn’t cause a shift in column references anyway.
Looks like you’re going to get to pay yourself a big dividend this quarter, assuming that sales go well....
Absolute references give you a lot of options
Depending on where you think you might need to copy your references, you should always consider using absolute references to make sure your formulas point where you want them to.
It can indeed. As important as references are, they can become unwieldy once your formulas get long and numerous. If your references start to confuse you, you can turn to a powerful feature of Excel called named ranges....
Named ranges simplify your formulas
This reference takes a moment to understand.
Even if you wrote it yourself, chances are, in the future you’ll forget its meaning and have to go back and forth to make sure you know where the reference is pointing. This formula takes a long time to understand.
When you used named ranges, you can replace those references with a plain-language name of your choosing.
And once you name your ranges, which by the way are by definition absolute references, you can drop them right into your formulas.
Let’s try out named ranges by incorporating one into your discount calculation formula. Instead of making the discount value reference B$4, let’s make it just discount.
Give your discount value the name
discount. To do this, select cellB4and then highlight and delete the reference at the top left. In that blank, typediscount.Rewrite the memory formula using your new named range.
Copy and paste your new formula to overwrite the old motherboard and hard disk discount formulas.
You just took named ranges for a spin for the first time. Did you find that they simplified your formula?
No problem. Let’s take a look at the data for the other computer lines and see what we can do.
With all this data, you’d have to write a ton of formulas
It could take you quite a while to come up with this.
Having more named ranges would be a help.
Your formulas would certainly be easier to read. But going through a whole bunch of cells and ranges and naming them individually takes a lot of time, too!
Excel’s Tables make your references quick and easy
When you click inside your data and click Table under the Insert tab, Excel gives you all sorts of options, including automatically generated range names. Once you’ve created your table, you can use a special syntax called structured references to simplify your range names.[3] Here’s how you make a table.
Select your data, then head over to the Insert tab and click Table.
Change your table name from Table1 to something more meaningful.
Now you have a table! Go ahead and start using it for structured references.
Easy, right? And you don’t have to worry about making your references point to sheets anymore, since Excel knows how to find your table in a workbook using the structured reference.
Structured references are a different dimension of absolute reference
Between the $A$1 style of references that you’ve learned and the references that you can name, you’ve spanned a broad universe of possibilities for referring to your data inside of formulas.
Let’s finish off our computer spreadsheet with some structured references....
Finalize your profitability projections. Using tables and structured references, calculate the profitability forecast of your mid-range and high-end computer models.
Assign your new price sheets as tables. For each table that describes a component, create a table using the button under the Insert tab. Make sure you give them each a name!
Using the same approach as for evaluating low-end PCs, calculate the minimum costs for each component. This time, use structured references in your formula.
Calculate expected profit margins for the other products. Use the sale prices below, and fill in the necessary formulas in row
15.Your sale prices
Mid-range: $600
High-end: $4,000
How profitable are your mid-range and high-end computers?
Assign your new price sheets as tables. For each table that describes a component, create a table using the button under the Insert tab. Make sure you give them each a name!
Using the same approach you had for evaluating low-end PCs, calculate the minimum costs for each component. This time, use structured references in your formula.
Calculate expected profit margins for the other products. Use the sale prices below, and fill in the necessary formulas in row
15.
Your profits are looking fantastic....
Your profitability forecasts proved accurate
Business is great, and your sales guys are happier than ever!
[3] Structured references are another one of those Excel 2007 and later–only features. It’ll come to Mac eventually.