How to Continue an Excel Formula Down a Column Keeping Some Variables Constant

Today I will talk about a small tip on MS Excel.

Excel is one of the frequently tools in the life of an IT personal. We mostly use it to generate some report, various tables, charts etc.
One of the most common use case include calculating values of various rows in a column based on a formula which includes a value of a cell.

For example, in the below screenshot, based on the principal and duration specified on top, we would like to know the value of Simple Interest & Compound Interest based on different rate of interests.

Most of us start using some formula for the first cell (for example in this case value for cell B5 is "=B1*B2*B4") and then drag (or copy) that to other cells which need to have same formula. But ALAS!!! What you get is broken formula with a lot of errors or incorrect value (for example G5 would end up having value formula "=G1*G2*G4" which is incorrect).

I bet, most of the people with primitive Excel skills end up modifying formula in each cell accordingly.

But don't worry. One of the Microsoft Excel's feature comes to our rescue so that you can save yourselves from the unnecessary rework and changes in individual cells

Excel has a special use of $ symbol when used in formula in combination with field references.
The $ tells excel not to adjust that address while pasting the formula into new cells. So, the value which should be used in our case for cell B5 is "=$B$1*$B$2*B4". When this formula is pasted into other cells (say G5), excel changes B4 field to G4 but leaves B1 & B2 as those two fields have been marked with symbol $ .

Now, lets see this $ symbol in more detail.

You might be asking, why do I need to but two dollar symbols (one before column [B] and other before row [2]).
$  symbol tells excel not to change the property after the symbol. If you used $B5, this means it will keep B as fixed but can change 5 to 6, 7 etc. If you used B$5, this means it will keep 5th row as fixed, but will continue to change the column as you copy the formula.
For example: In the below example, we want to calculate the product of column header & row header for each of the cells.

In this we would start with the value for B2 as "=B1*A2". However, when we copy this to C2, we want to make the value as "=C1*A2". Means, for first value, we want to change the column keeping row as 1. So, our value would be "=B$1*A2". This would ensure that row remains 1 for each cell, but column keeps changing.
Now, when we copy this to B3, the final value should be "=B1*A3". Means, for the second value, we want to change the row based on the row of the formula cell, but we want to keep the column fixed as A. So, our value would be "=B1*$A2".
If we combine both the cases (for movements in x & y), the final value would be "=B$1*$A2", means, for first value, use 1st row keeping column as variable based on your cell; and for second value, use column A as fixed, but change the row based on your cell.
So, if you copy this field value in all the fields from B2 to I10, you will get the proper results.

So simple to achieve this with MS Excel's $ symbol feature

Hope this helped you. Feel free to provide your views in comments below.

robersonwhouses.blogspot.com

Source: https://www.technosfirst.com/blog/2-excel-formula-variable.html

Share this post