07 Dec 2021

Posted on:

07 Dec 2021

0

# Can you please illustrate with very simple example the usage of \$ function.

Can you please illustrate with very simple example the usage of \$ function.

Posted on:

07 Dec 2021

0

Unfortunately, I don't think there's a more simple example (that is still good and correct!) for explaining the fixing of cell references than the one we've applied. In my opinion, exercising is the best way to make sure you get used to this extremely useful tool. So, I hope you don't mind, let me wrap up on how the dollar sign can be used.
When it comes to the content of a certain cell, whether you will use it or not, it does not matter. It will matter only when you copy the cell's content in other cells, or when you drag the information to other cells.

Thus, assume you have some values from D4 to E6, that are applied to certain cells in H4 to J6 (as is the case in the video). This means data from D4:E6 will be referred to in the area of cells H4:J6.

Focus on cell D4.

If you have D4 and drag in different directions (i.e. up, down, to the left, or to the right), all numbers and letters will change. E.g. if you refer to D4 in a formula in cell H4 and copy (or 'drag') this formula to the right (i.e. to cell I4), the reference (D4) will change to E4. I.e. we have a letter, standing for a column position, that has changed. Alternatively, if you drag this formula down (i.e. to cell H5), the reference (D4) will change to D5. That is, we will observe a number changed (and a number stands for indicating a row position, not a column position).
Now, if you fix the letter D in D4 (which is done by typing \$D4), nothing will change when you drag H4 to I4. Why? Because the letter D has been fixed, i.e. it cannot change. Nevertheless, H4 will still become H5 because the number 4 has not been fixed in D4.

If, instead, you fix the number 4 in D4 (done by typing D\$4), I4 will still refer to D4 when the formula from H4 has been dragged to the right, while nothing will change when you drag the formula from H4 down to H5. I.e. it will still refer to D\$4.

Finally, if you fix both, \$D\$4, you will refer to cell D4 in whatever cell you paste the content of H4. Both row and column positions of D4 are thus fixed, so no changes will be observed.