Entering functions

Functions

A function is a predefined formula that operates on one or more values and returns one or more values. 

You have seen how using the SUM function can save you a lot of typing. For example, the formula

=SUM(A1:A15)

is considerably shorter than =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12+A13+A14+A15 

All functions consist of a function name (such as SUM) followed by a set of arguments (separated by commas) in brackets. The arguments specify the values or cells to be used by the function. 

The maximum number of arguments allowed in a function is 30. In the example above, there is just one argument, namely, A1:A15. 

A few functions have no arguments but the brackets are still needed, as in the following examples: 

=2*PI()  which calculates the value of 2p

=RAND()  which returns a random number that is greater than or equal to 0, and less than 1. 

If you enter a space between the name of a function and the opening bracket, Excel will warn you that it has found an error.

Excel has more than 300 built-in functions. Some of them perform complex calculations but the examples that follow are of the more straightforward kind.

If there is not a built-in function that does precisely what you need, you can actually create your own custom functions but that involves using Visual Basic code and will not be discussed in this document. 

Entering functions 

You can enter a function into a worksheet by

• typing it in (as you did in section 2.2)
• clicking the Insert Function (fx) button (to the left of the formula bar)
• selecting Insert | Function

If you type in the name of the function, use lower case letters. Then, when you either press the Enter key or select another cell, Excel will change the name to uppercase providing it has been typed correctly. So, if lowercase letters remain, you will be alerted to the fact that you have typed the name incorrectly and you can correct it. 

When you click the Insert Function button or select Insert | Function, the Insert Function dialog box will be displayed. 

If you know the name of the function you want to use but cannot remember which category it is in, in the Insert Function dialog box, either,  

type the name in the Search for a function: box and click Go

or, 

select All in the Or select a category: box (this gives a complete alphabetical list of available functions). 

If you feel that you might need extra help in creating your function, click on the Help on this function button in the bottom left corner of the Insert Function dialog box. The Microsoft Excel Help window will then be

displayed (as in the picture that follows). 

To illustrate how to build up a function, examples of various types of function will now be considered