ECDL – Excel 2010 Formula & Functions book Excel has functions which save you having to write formulas to carry out some calculations which are used quite frequently. You can use a function to find the average of a group of numbers, or to find the highest or lowest number in a range of cells. Pride in Pompey 5/12/2014
Table of Contents
2
Sum The function SUM works out the sum of a range of numbers. In the cell, you have to type =SUM(_ _:_ _)
Remember always start your formula with the’ =’, otherwise it will not calculate your answers.
3
Average The function AVERAGE works out the average of a range of numbers. In the cell, you have to type =AVERAGE( _ _:_ _)
You can either type the cell references or click and drag in the worksheet to select the cells you want to use. (You don’t need to type the closing bracket — Excel will add it automatically when you click or press Enter.)
Max & Min The function MAX finds the largest value in a range. The function MIN finds the lowest value. Type = followed by the function name and then give the range of cells in brackets: =MAX(D3:D10) =MIN(D3:D10)
4
Rounding numbers Sometimes, you may not want to see a rounded version of the figure resulting from a calculation. If you only want to see the rounded figure, you can reduce the number of decimals shown in the cell, but if you want to see both rounded and unrounded figures you can use the ROUND function in another cell. Type =ROUND( followed by the cell reference, then the number of decimal places you want to show:
COUNT
The function COUNT returns the number of cells in a range. In this example, the function in cell F8 counts the number of sponsors by counting the cells in the donations column, the range C3:C18.
COUNTA The COUNTA function returns the number of cells in a range which hold a value, which may be a number or text. In this example, the function in cell F9 counts the number of sponsors who have paid their donation, in the range of cells D3:D12. 5
IF Function
The IF function offers a way of testing data and making decisions. For example, you might decide that if it is sunny, then you will go out, but otherwise you will stay in. The IF function uses the same method. If the data in a cell matches a condition that you set, one result will follow. If not, something else will follow. To compare the data in cells to make these decisions, the IF function uses the comparison operators: = equals > greater than < less than. The IF function takes three arguments:
The logical test is the mathematical statement that tests the data. The next part is the value to show in the cell if the data pass the test. The last part is the value to show if the data fail the test.
6
In this example, the logical test is C3>59: it tests whether the value in C3 is greater than 59. If the result of the test is TRUE (ie the value of C3 is greater than 59), the result shown in the cell is PASS — the student has passed the exam. If the value is not greater than 59, the student has failed and the cell shows FAIL. The arguments are in brackets and are separated by commas. If one or both of the results to show in the cell is a word, you must put it inside quotation marks in the cell: “PASS”. You can copy a cell with a function, and the cell references will update to suit the new position. Using the IF function to carry out a calculation You can use the IF function to carry out a calculation as the result of the logical test. The calculation must be provided as an argument.
In this example, the logical test works out whether a sales executive’s sales meet a target of £50,000. If they do, the executive gets a 10% bonus, so the second argument calculates 10% of the sales figure (in B2). This is B2*10%. If the sales don’t meet the target, the executive gets a 5% bonus, so the last argument calculates 5% of the sales figure — B2*5%. Using the IF function to carry out a calculation In this example, the logical test works out whether a sales executive’s sales meet a target of £50,000. If they do, the executive gets a 10% bonus, so the second argument calculates 10% of the sales figure (in B2).
Some common errors
If there are errors in a function or formula, the worksheet will not give the right values. To help avoid this problem, Excel alerts you with an error message if there is an obvious mistake in your formula
Some common errors #REF, #VALUE, #NAME and #DIV/0! The error message #REF appears in a cell if the function or formula refers to a cell reference that is not valid. The most common reason for this is that a cell has been deleted. The quickest way to fix it is to undo the action that removed the cell. The error message #VALUE appears if you miss out a function name. It also appears if you try to use a cell that contains text in a formula or function that expects a number. The error message #NAME? appears if you type the name of a function wrongly or if you miss out the brackets needed by a function. 7
The error message #DIV/0! appears if you set up a calculation which tries to divide a number by zero. This is an impossible calculation. As Excel considers an empty cell to have a value of zero, the error message occurs if you have left a cell empty that is used for division in a formula.
Circular references
A circular reference occurs if a cell holds a function or formula that refers to itself in a calculation. It happens if you drag too far when selecting a range, and include the cell in which you are entering the formula or function. If you enter a circular reference, Excel displays a dialogue box immediately to tell you. Click OK to fix the error. Information about circular references is displayed by the Help system. Update the formula to remove the circular reference.
Freezing rows and columns
When you scroll down or across a worksheet you can no longer see the headings for rows and columns. This can make the worksheet difficult to use. You can â&#x20AC;&#x2DC;freezeâ&#x20AC;&#x2122; some cells so that they stay visible and are not affected by scrolling. Using this to keep headings and titles on screen makes it easier to use the worksheet. To freeze titles along the top of a worksheet, you need to freeze by row. Select the row below that you want to keep on screen, then click on the View tab and click on the Freeze Panes button. The first option in the submenu freezes all rows above the selected row.
To freeze headings along the side of a worksheet, you need to freeze by column. Select the column to the right of that you want to keep on screen, then use the first option in the Freeze Panes menu. The columns to the left of the selected one will stay visible as you scroll across the worksheet. To freeze by both rows and columns at the same time, click the single cell that is to the right of the column and below the row you want to freeze, then use the first option in the Freeze Panes menu. In this example, using C4 would freeze all the headings in place.
8
To return the worksheet to normal, open the Freeze Panes menu and select Unfreeze Panes which has replaced the first option at the top of the menu.
Adding headers and footers to a worksheet
A header is a line of text printed at the top of every page. Footer is text printed at the bottom of every page. A header is often used to show the title of a spreadsheet, the name of the person who created it, the date, or the version of the spreadsheet. A footer often shows the page number. These are added as automatic fields in Excel, so that they update automatically if the information changes. Headers and footers appear on printed worksheets but are not shown on screen except in Print Preview. They make printed worksheets easier to use. Click on the Insert tab and click on the Header and Footer button.
The Design tab opens, and the header area is displayed at the top of the worksheet for you to add text to the header.
The header has three areas: left, centre and right. Click in an area to make it active and then you can type text or pick an automatic field from the ribbon:
Useful options include the page number, date and file name. The automatic fields are added like this to the header or footer: &[Time], but are replaced with the correct information when you preview or print the worksheet.
9
10