Mixed Referencing in Excel

Mixed Referencing

This calculation is used when you want to find out the value of a fixed cell and calculate it with a range of cells. This may be used when you have a percentage and want to calculate values in a list e.g. in a sales table. One part of the reference is fixed and the other part is not. The fill handle is then used to copy the formula along the list of cells. The formula remains at one cell and the other part continues wherever the formula is copied.

1. Create the following table:

2. In this example, we want to find out the discount based on the price of an item. This will calculate the discount for the item if it is priced at €5, €10, €15 and €20. This calculation will also calculate the discount applied to each price at different percentages including 5%, 10%, 15% and 20%. For example, using mixed referencing, we can check what discount will be applied to the item if it is €15 and the discount is 10% in cell D6.

3. Include the dollar sign before the first cell reference and in the middle of the second cell reference: In cell C4 type in: =$B4*C$3

4. Use the fill handle to copy the formula horizontally then vertically

5. This has now calculated the discount based on the price of the item and the rate of discount applied. Mixed referencing can be applied to a range of different scenarios such as calculating loans based on varying percentages or savings based on variable rates of interest.

6. Save the workbook as “Mixed Referencing”

7. For more information about Relative Addressing and Absolute Addressing, click on the link below:

https://www.digidiscover.com/post/the-difference-between-relative-absolute-addressing-in-excel