Did you ever drag a formula and it behaved dramatically. Did you ever faced a problem as mention below:
In the above picture I have tried calculating the Price of the products in the year 2014 by using Price of Product 1 in 2013 (cell G8) and Price change factor (cell H4). The formula in the first cell is :
G8+(G8*H4)
When I drag the formula for Product 2 to Product 5, it shows a very abnormal result. The reason why formula is showing a wrong result is explained in the below picture:
The formula is taking cell H7 instead of H4, which is Price Change Factor to calculate the Prices in 2014.
Why did the reference change? Answer to this question is we have not fixed the cell H4 in the formula or in other words we did not make absolute reference to the cell H4. The referencing used in this formula is Relative, which changes with every increment/decrements of row and column.What should I do to make it absolute/fixed? You just need to append $ (dollar sign) to the range and make it $H$4 instead of H4. With these dollar signs, your formula looks something like this.
Shortcut to add these dollar signs is F4. Write the formula in the first cell :G8+(G8*H4). Go in formula editing mode by pressing F2 on the formula cell, select the range you want to make absolute and press the button F4, drag the formula in below rows, for all products. Here in this example you really don't need to fix column since your dragging the formula in the rows. To fix the only the row you need to append dollar sign only before row number 4 and not before column index H. The formula looks something like this: G8+(G8*H$4). If in case you want to drag the formula in columns in the same row, you need to fix column by appending $ before H and not before 4 (your formula will be G8+(G8*$H4) ). Press F4 key repeatedly to add/remove dollar sings before row/column index.
Hope this is useful. Visit us on excelvba.org
No comments:
Post a Comment