Excel Functions
Functions are at the core of most of Excel's practical value.
Microsoft Excel is ideal for organising large amounts of data.
In Excel, the simplest way to perform calculations on information in cells is using functions. Simply put, these are commands for working with information, generally in the form of a formula.
Some Basics
Visually, the Excel window can seem complicated at first glance, but many options that are probably familiar from using Word are in the same place in the menu bar, such as Open and Print in the File menu.
Entering Functions
The best way to start working with functions is to launch the Insert Function wizard by clicking on the `fx' button next to the formula bar.
This brings up a window for searching for functions using plain English. Simply type in a description of what you want to do, and Excel will recommend a function. You can also read a brief summary of each function by clicking on it.
Functions can use information in different ways. The information can either be specified in the same cell as the function, or the function can draw on the contents of other cells. When using the latter option, you can either specify each cell individually with a comma in between (for example A1,A2,A3) or choose a range of cells, entering the first and last separated by a comma, such as A1:B2 to specify A1, A2, B1 and B2.
When writing a function, cells can easily be selected by clicking on them, or clicking and dragging to select a range. When doing this for cells on another worksheet, click on the relevant worksheet tab, then on the cell or cells to select, then return to the original worksheet.
Excel formulas must be input precisely. A stray character or space, and the formula won't work.
With experience, the Insert Function wizard can be sidestepped and instructions typed straight in. To do this, click in the text box in the formula bar, type in '=' followed by the name of the function and place the cells to be used in the function in brackets. In Excel, each cell reference or range of cells is called an argument.
The same effect can be achieved by typing the formula straight into the cell in question. Unless typing, though, the cell only shows the results of a formula, with the formula itself only visible in the formula bar.
Editing Functions
To edit a function, move the cursor over the cell, click, then press F2. Any cell references are written in colour with a colour border around it to help show how a function works.
Revealing Functions
It is possible to see the contents of all functions by holding down Ctrl and pressing the ~ key.
Naming Cells
A range of cells can also be given a name for use in functions. To do this, click and drag over a range of cells and type the desired name in the formula bar. The range name must not have any spaces but can contain both upper- and lower-case text.
Using Comparisons
Some functions use comparisons with other cells, such as equals (=), less than (<), greater than (>), less than or equal to (<=) and greater than or equal to (>=).
Common Functions
SUM
The SUM function is used to add up the numbers in cells or in a function itself. Up to 30 cell references or numbers can be used, separated by commas.
To add the contents of cells Al and A2 together, for example, type'=SUM(A1,A2)'.
SUM can only add figures that Excel recognises as numbers; if a cell has been formatted as text by preceding the number with inverted commas, the contents of that cell will be ignored. Also be careful if you have used True or False formatting because they will be used as 1 and 0 respectively in any calculation.
SUMIF
The SUMIF function takes SUM one step further by only adding together the contents of cells within a range that meet certain criteria.
There are three elements of this function. The first is the range of cells to look in, the second is the criteria to look for in this range and the third is the numbers to be added should the criteria be met. If the third argument is the same as the first, there is no need to repeat it.
The criteria can be a figure and can be defined using the equals, greater than or less than signs (=,>,<), or by specifying text.
In the example shown below, to add up numbers bigger or greater than 10 in the range D3 to D7, type:
=SUMIF(D3:D7,">=10")
=SUMIF is the function, D3:D7 describes which cells the function applies to, and >=10 tells the function to work only on numbers in those cells that are greater or equal to 10.
To add the contents of a range D3:D7 based on the contents of the range E3:E7, use the following function:
=SUMIF(E3:E7,"Jo",D3:D7)
This formula looks for the text Jo in the E column. When it finds it, the contents of the corresponding D column is added to the total.
AVERAGE
The AVERAGE function displays the average of the cells given as an argument. This function displays the average calculated by adding up all of the numbers and then dividing by how many numbers there are. So the average of 5, 10 and 15 is 30/3, or 10.
There are separate functions called MEDIAN and MODE for working out these kind of averages.
To work out the average for the cells B1, B2 and B3, enter the function:
=AVERAGE(B1:B3)
If there are empty cells in the range, they are not counted and won't affect the result. Cells containing a zero will affect the results, however.
COUNT
The COUNT function displays the number of cells in the given range that contain a number. To display the number of cells containing a number in the range B3, B4, B5 and B6, enter the function
=COUNT(B3:B6)
There are several related functions, such as COUNTIF for selecting the condition for counting cells, COUNTA for counting cells with numbers or text and COUNTBLANK for counting blank cells.
IF
Logical functions, of which IF is one, allow decisions to be made about cell contents based on the result of a True or False argument.
Using the IF function, the first part of the formula makes a decision as to whether something is true or false, for example B5=10. If the figure in B5 does equal 10 it is considered True and the function performs one action; otherwise it is considered false and it does something else. Other functions such as AND and OR are used to make a decision based on more than one criteria.
We have already looked at some functions that use decisions in this way, such as SUMIF. The disadvantage with SUMIF is that there is no alternative action for when the criteria are not met. Using the IF function alone gives much greater control.
The simplest way of using IF is to give a judgement on a cell. For example, a spreadsheet with school grades can automatically decide if a student has passed or failed if the pass mark is 51. Enter the function:
=IF(B2>=51,"Pass","Fail")
B5 is the cell with the score. The first argument is the decision, in this case whether B5 is greater than or equal to 51. The second argument is the value for the cell if this is true, and the third is the value if it is false.
For more complicated grading, it is possible to use the IF function again to further separate cells. Up to seven IF functions can be used in a single formula, and is called nesting. When using IF, be careful to get the brackets in the right place - it is often a good idea to write a complicated function like this down on paper first.
To give a practical example, if a score greater than 80 gets an A, 70-79 a B, 40 to 69 a C and anything less than 40 is a fail, use the following function:
=IF(B2>80,"A",IF(B2>70,"B",IF(B2>40,"C","Fail")))
The function looks at the contents of cell B2. If it is greater than 80 it displays an A; if it is less, it goes to the next IF function. If it is greater than 70 it displays a B or goes to the final IF. If the figure is greater than 40 it displays a C, otherwise it displays Fail.
MAX
The MAX function displays the largest number in a range of cells. To display the largest figure in the cells B2, B3, B4 and B5, enter the following:
=MAX(B2:B5)
Miscellaneous
ICT Tutorials
- Databases
- Multi-Media
- Spreadsheets
- Word Processing
- Web Site Authoring