The Excel Function can be allocated to a particular Cell (Absolute), or relative to the current position (Relative). If relative, then the function, called by name in one cell, is applied based on taking the current position, and the applying the cell references in the equation from there. If Absolute, then the actual cell references are used.
Use a Relative Function
Reference by name:
1. Place cursor anywhere in the spreadsheet.
2. Choose Insert | Name | Define.
3. Enter a Name for the function. This will be the name which will be called in cells as though it is a value.
4. Enter an equation in the “Refers To” box. This needs to be worked out so that where you are going to want to use it, it will reference the required places based on the current cell, and the relative reference of cells being used.
5. Choose OK.
Eg if the function is always going to multiply the
next adjacent cell to the right by two, then do the following:
a) Click in A1
b) Choose Insert | Name | Define
c) Enter “Test” for a name.
d) Enter “=B1*2” in the Refers to box. (This will mean any use of =Test will work out to be the quantity in the Cell to the right times 2, as though the current cell is always A1, then the cell one to the right will always be B1).
e) Choose OK.
f) Click on any cell and type “=Test”.
g) To test, enter a value in the cell one to the Right.
h) The result will be where you typed “=Test” will show the value of the cell to the right multiplied by 2.
Use an Absolute Function
Reference by name:
1. Place cursor anywhere in the spreadsheet.
2. Choose Insert | Name | Define.
3. Enter a Name for the function. This will be the name which will be called in cells as though it is a value.
4. Enter an equation in the “Refers To” box. This needs to be worked out so that the actual cell reference is entered with $ before the Column and Row. Eg =$B$1*2
5. Choose OK.
Eg if the function is always going to multiply the
contents of B1, then do the following:
a) Click in A1
b) Choose Insert | Name | Define
c) Enter “Test” for a name.
d) Enter “=$B$1*2” in the Refers to box. (This will mean any use of =Test will work out to be the quantity in the Cell B1 times 2.
e) Choose OK.
f) Click on any cell and type “=Test”.
g) To test, enter a value in the cell B1.
h) The result will be where you typed “=Test” will show the value of the cell B1 multiplied by 2.