We have treated relative references and absolute references in excel, now it is time for mixed references. Yes, you might have guessed, the mixed reference is a combination of the two types of reference stated earlier (relative & absolute reference). In today’s post we would take you through all you need to know about it with the help of an illustrative example. Let’s get right into it.
Mixed reference example
In our relative reference and absolute reference posts, we treated examples of a Telecommunications company data offers illustrating how to correctly refer to input cells to return revenue numbers. In this example, we are adopting the same Telecommunications data offers, however, rather than calculating revenue numbers, we would be forecasting our sales count with mixed anchors.
In our example, we are to forecast sales count for each data plan using the average daily quantity on the vertical axis and number of days on the horizontal axis. Our goal is to achieve this with a single formula to copy and paste to other cells and for this we need to use mixed references. We must anchor column B (average daily qty) whilst keeping the rows relative. On the flip side, we must also anchor row 2 (number of days) whilst keeping the columns relative.
Let’s see the steps below:
Step 1: Anchor column B in cell C5 by pressing the F4 key three times or keeping a dollar ($) sign before the Column letter
Step 2: Now add a multiplication sign (*), select C2 and then press the F4 key twice to anchor row 2. You can simply type a dollar ($) sign if you choose not to use the F4 key.
Step 3: Press the enter key, and then copy the formula in cell C5 and paste to all other cells using CTRL + C to copy and CTRL + V to paste
Knowing how and when to apply mixed references could be a huge time save for you, as the default reference for excel is relative reference and in our examples above excel would keep adjusting the position of our input as we move right and down. For example, February sales forecast calculation for 500MB would select C6 as Qty and D3 as number of days to have C6*D3 as our final formula which will result in an error. So, save yourself the headache of correcting these references by using mixed references from the get-go.
