![\"image\"](\"https:\/\/ingmarverheij.com\/wp-content\/uploads\/2011\/05\/image14.png\" "\\"image\\"")
<\/a><\/p>\nEach cell can contain data or a formula. With a formula your able to transform the data from other cells to whatever you may need. You\u2019ll probably want to re-use the formula in different rows, columns or worksheets.<\/p>\n
<\/p>\n
A formula contains a function (like min, max, average, etc) and a range of data (like A:A or A1:A10).<\/p>\n
For example I\u2019ve filled row 1 to 10 on column A with the corresponding numbers.\u00a0 In Cell B1 we\u2019ve created a formula that calculates the average of these 10 cells : =AVERAGE(A1:A10).<\/p>\n
PS: The screenshot says \u2018gemiddelde\u2019 which is Dutch for average. <\/em><\/p>\n <\/p>\n Now we copy the content of cell B1, which contains the formula, and copy it to cell D5.<\/p>\n The\u00a0 result of cell D5 is \u201cdivision by zero\u201d. When we look at the formula we see that Microsoft Excel transformed both the column and the row corresponding to the location where we moved it (+2 columns, +4 rows).<\/p>\n Altough this might be usefull in some cases, in some cases it isn\u2019t.<\/p>\n <\/p>\n There is a little trick which helps you setting constraints when copying formula\u2019s. The use of the $ sign. If you place a $ sign in front of a column or a row it will become static. It won\u2019t be transformed when you copy the content of a cell.<\/p>\n Let’s take a look at how this works.<\/p>\n When we change the content of cell B1 to =AVERAGE($A1:$A10) where setting a constraint on the row, row A is now static. When we copy the cell to D5 the formula in D5 will be =AVERAGE($A5:$A14).<\/p>\n <\/p>\n When we repeat the same thing but instead of setting a constraint on the row we\u2019re setting a constraint on the column. The content of cell B1 will be = AVERAGE(A$1:A$10).<\/p>\n Now the formula in cell D5 will be =AVERAGE(C$1:C$10). The column is changed, but the row\u2019s are not.<\/p>\n<\/a><\/p>\n<\/a><\/p>\n<\/a><\/p>\n