See all Microsoft Excel tips
This formula will contain all four reference types. While entering the first formula in H6, you will want to base the commission calculation on the January sales in E6. As you copy the formula from January to February, you will want the E6 reference to be able to change to F6. As you copy the formula down to other rows, you will want the E6 to change to E7, E8, etc. Thus, the E6 portion of the formula needs to be a relative reference and will have no dollar signs.
You will multiply the sales times
the base rate in B1. As you copy the formula to other months and rows, it always needs to point to B1. Thus, you need to use dollar signs to before the B and before the 1: $B$1.
To incorporate the product bonus, you will need to multiply sales by the Product Rate in column C. All of the months in row 6 will have to refer to C6. All of the months in row 7 will have to refer to C7. Thus, you need a mixed reference where column C is locked. Use the address of $C6.
Finally, the VP of Sales added the monthly profit sharing bonus. The
entire commission calculation is multiplied by the bonus factor shown
in row 1. The January commission calculation uses the factor in E1. The
February factor is in F1. The March factor is in G1. In this case, you need to allow the formula to point to different columns but always to row
1. This requires a mixed reference of E$1.
Now that you have the four components of the formula, you can enter this formula in E6, as shown in Fig. 180: =E6*($B$1+$C6)*E$1.
Result: As shown in Fig. 181, you have created one single formula that can be copied to all columns and rows of your dataset.
Summary: The concept of relative, absolute, and mixed references is one of the most important concepts in Excel. Being able to use the right reference will allow you to create a single formula that can be copied everywhere.
See all Microsoft Excel tips