Referring to a cell
One difficulty which stumps even seasoned Excel users is how to refer to the contents of one cell in the current cell. For example, if cell A2 should contain the same contents as cell C2 simply type =C2 in cell A2.
Linking cells like this ensures that when the value in C2 changes, the value in cell A2 will change to match it. You can achieve a similar result across worksheets by prefixing the cell reference with the worksheet name.
For example, this formula refers to the contents of cell C2 on Sheet1: =Sheet1!C2.
Multiply and Add
Often when you are making calculations you need to multiply the contents of one column by the values in a second column and then add the results. You might do this, for example, if you have a list of product numbers in stock and you need to multiply these by the cost price and sum it to obtain an inventory value.
To do this use the SUMPRODUCT function, the syntax of which is =SUMPRODUCT(FirstRange,SecondRange).
For example, =SUMPRODUCT(A2:A25,B2:B25) will multiply each of the cells in the range in column A by the corresponding cell in the range in column B and then total the result.
Making choices
The IF function lets you make a choice in your worksheet. For example, if cell A2 contains a value of Y or N depending on whether a discount is applicable, you can use the value in that cell to calculate the discount in another cell. If the discount is 10% this formula will calculate the discount on a price in cell A3:
=IF(A2 =”Y”,0.1*A3, 0)
The formula checks cell A2 to see if a discount is to be allowed and, if so, the discount is calculated using the total price in cell A3. If not, the discount is set to 0.
Finding Roots
Excel has a special function SQRT for finding the square root of a number. So, for example, the square root of 25 can be calculated using =SQRT(25). There is, however, no corresponding function for calculating the cube or any other root of a number. You can, however, calculate this using a simple formula if you know that the square root of 25 can be written mathematically as =25^(1/2). By extension, the cube root can be calculated by typing =25^(1/3).
DIY functions
It is possible to create your own functions in Excel. Choose Tools > Macros > Visual Basic Editor and, in the Project - VBA Project pane, select the current file and choose Insert > Module. Type this function into the dialog and return to your worksheet:
Function Commission(Sales) As Currency
Commission = Sales * 0.05
If Commission > 1000 Then
Commission = 1000
End If
End Function
To test your function type this formula into a worksheet:
=COMMISSION(20000)
The function calculates commission at 5% of the amount of sales. If the 5% value is more than £1000 then the commission is pegged at this amount - to check this, test the function with a very large number. Functions created this way are only able to be used in the current workbook.
Summing conditionally
In some instances you may want to sum a column of numbers depending on the contents of that column. For example, if you want to total the sales figures in the range B3:B35 but only where those values are greater than 100, this SUMIF formula will do the work:
=SUMIF(B3:B35,”>100”)
As you can see there are plenty of functions in Microsoft Excel to help you produce spreadsheet results without having to do a lot of effort in making the calculations manually. In addition to speed, another benefit of using functions like these is that they automatically update when the data in the worksheet changes. |
The NetWorkDays function calculates the number of workdays between two dates taking nominated holidays into account. 
You can create your own custom functions using VBA which can be used in the current workbook.