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

Read More

Adding rows and columns of numbers

• Scroll down the worksheet to the table with the heading Sneeze count for Tuesday.

The row Totals and the column Number of sneezes in eight hours can be filled in quickly by using the AutoSum button. Again, you select your data plus the area where the totals are to appear as follows:

• Highlight the cells from C20 to E26 inclusive.

• Click on the AutoSum button on the toolbar.

• Click away from the selected area.

This procedure is often used when adding up tables of costs (currency).

Read More

Adding a column of numbers using the SUM function

Now try using the SUM function:

1. Click in D13.
2. Type  =SUM(D7:D11)
3. Press the Enter key.

If you wish, instead of typing D7:D11 you could just highlight those particular cells. Excel would fill in the description of the range and supply the closing bracket.

Read More

Adding a column of numbers using the S button

Adding numbers

There are a couple of easy ways of adding up a column of numbers. To

access some suitable data,

• Click on the worksheet tab Sneezes.

You will see a table with the heading Sneeze count for Monday. The totals are to be filled in as described below. 

Adding a column of numbers using the S button

 

Try using the AutoSum (E) button to add up a column of numbers:

• Highlight the cells from C7 to C13 inclusive. (Note that Excel displays the message Sum=128 on the status bar.)

• Click on the AutoSum (S) button on the toolbar.

• Click away from the selected area. 

The answer 128 is displayed in C13; the formula in that cell is actually =SUM(C7:C12).

• Click in C13 to see that formula.

SUM is one of the built-in functions in Excel, probably the one most often used. 

If you are sure that there is no ambiguity as to which cells are to be included in the calculation, you can simply select the cell below the numbers that are to be used and click the AutoSum button.

Note:  Clicking on the arrow next to the AutoSum button gives quick access to the functions Average, Count, Max and Min, and to all the built- in functions (select More Functions).

Read More

Absolute Cell References

Sometimes you will need to work with absolute cell references that refer to cells by their fixed position in the worksheet. 

1. Scroll down the worksheet until you can see the range B20:F24 with data about VAT. 

First, complete the VAT column

1. Click in C21
2. Type  =B21*F21 and press the Enter key The formula in C21 gives the correct answer (｣19.60) but is not suitable for copying. To demonstrate this, 

 

Copy the formula in C21 to C22.

The value shown will be ｣0.00. This is because F21 became F22 when copied and the value in F22 is zero.

When you are going to copy a formula and do not want a cell reference to change, you should use an absolute cell reference. A dollar symbol, $, is used to fix a reference. When placed in front of a column name it fixes the column and in front of a row number it fixes the row.

So, for example, $F$21 means always column F and always row 21. 

Note: If you click anywhere within a cell reference in a formula, and press the F4 button, dollar symbols will be inserted for you automatically. You can have mixed references such as $Z34 (column is fixed, row is relative) and Z$34 (column is relative, row is fixed). Keep pressing F4 if you wish to have mixed references.

 

Now change the formula in C2

1. Double-click in C21.
2. Edit the formula to read  =B21*$F$21
3. Press Enter.
4. Copy the formula in C21 down to cell C24 and check that the  resulting values are correct. 

Finally, complete the Cost with VAT column

1. Click in D21 and enter a formula for the cost including VAT (cost without VAT plus the VAT to be paid on each item
2. Copy that formula down to D24. Your results should be as shown below

When entering formulae you can, if you wish, refer to cells in other

worksheets of the same workbook and to cells in other workbooks 

Read More

Copying a Formula

A formula can be copied to another cell in the same way as text or numbers

1. Click in D11.
2. Click the Copy button.
3. Click in D12.
4. Click on the Paste button (and ignore the smart tag if it appears). 

Another way of copying is to use the Auto Fill handle:

 

1. Click in D12.
2. Move the cursor to the bottom right-hand corner of the cell. The cursor shape will change to a small black plus (+) sign.
3. Hold down the mouse button, drag to cell D14 and then release the button. 

The formula in D11 was =B11+C11. When that was copied and pasted into D12 it became =B12+C12. This is because Excel, unless instructed otherwise, works with relative cell references. It thinks about where cells

are in relation to the cell in which the formula is entered.

This means that Excel thinks of that particular formula as take the contents of the cell two columns to the left of the cell containing the formula then add the contents of the cell one column to the left of the cell containing the formula

Read More

Clicking on the cells to be included in a formula

Now, try clicking on cells instead of typing their cell references

1. Click in D11 and type =
2. Click in B11 and note how Excel enters that reference into the formula.
3. Type  +
4. Click in C11.
5. Press the Enter key.
6. Check that the answer is 228

Read More

Typing The Cell References in a Formula

First, try typing the cell references in a formula

1. Click in D10 and type  =B10+C10  
2. Press the Enter key. (You could type =b10+c10 if you prefer. Excel does not mind whether the cell reference is in upper or lower case letters.)
3. Check that the answer is 107

Read More

Constructing your own formula

In order to practice creating a formula, try the following: 

1. Think of an integer (whole) number. (For checking purposes it is probably advisable to make it less than 100.)
2. Enter a calculation in E3 that will carry out all the following steps

•  add 20 to the number you thought of
•  multiply the result by 4
• subtract 8 from the result
• divide that result by 2
• subtract twice the number you thought of 

The answer should be 36. If it isn’t, check your formula, paying particular attention to the brackets, and edit it as necessary.

Read More

Using Cell References

In the examples so far, you have used actual values. Formula can also contain cells references. You can practice using cell references by adding up five pairs of numbers. 

The range B10:C14 already contains the data shown below

7	100
12	216
15	130
19	80
24	50

Read More

Experimenting With arentheses

Now try editing the formula in B3. Put parentheses round different parts of the expression, as suggested below, and see how they affect the answer.

When you have more than one pair of brackets in a formula, Excel displays them in different colours on the formula bar. 

Formula	Answer
=(2*9)+12/(6-4)	24
=2*(9+12)/6-4	3
=(2*9+12)/6-4	1
=2*(9+12/(6-4))	30

Read More

Editing formula

A formula can be edited in the same way as any other entry in a cell.

To delete a cell reference or any other character, select it and press the Backspace or Delete key.

To replace a cell reference, select it and click in the replacement cell.

To add to the formula, position the insertion point where the extra characters are to go and type them in.

To undo changes that have not been locked in, press the Esc key or click the Cancel button (red cross) on the formula bar.

To undo changes that have been locked in, click the Undo button on the Standard toolbar, press Ctrl/Z or select Undo from the Edit menu.

Read More

Order of priority of operators

The mathematical operators are shown below

(-)	Negation (negative number)
(%)	Percent
(^)	Exponentiation (to the power of)
(*  And  /)	Multiplication and division
(+ And -)	Addition and subtraction
(&)	Addition and subtraction
( =   <    >    <=    >=    <>)	Comparison

They are listed in decreasing order of priority starting with negation which has the highest priority (done first) and ending with comparison which has the lowest (done last). If a formula contains operators with the same

priority, they are evaluated from left to right. 

If you want to alter the order of evaluation, use parentheses (brackets) to group expressions. Any parts of a formula that are in parentheses are done first. 

It is important to fully understand the order in which Excel carries out a calculation.

1.  Click in B3 and type  =2*9+12/6-4
2.  Press the Enter key.
3. Make sure that you understand why the answer is 16.

Read More

Creating & Entering Formula

The real strength of a worksheet lies in its ability to carry out calculations. A Worksheet that does not contain calculations could have been created using a word-processing package instead.

• Activate Excel.
• From the File menu, select Open.
• Click on the 6 to the right of the Look in: box. 
• Select the drive called work file on ‘Dudley’(T:).
• Double-click on the folder its.
• Double-click on the folder Excel.
• Select the file Data_for_Calculations.xls and click the Open button

All formula begin with an equals (=) sign. If you forget to type the =, therest of the line will probably be entered into the cell as a piece of text.

1  In the Data_for_Calculations workbook, click in B2 and type =8+4 The formula will be displayed on the formula bar.

2  Press the Enter key (or one of the arrow keys) to lock the formula in.The cell B2 now contains a formula (=8+4), but the answer 12 is displayed on-screen.

3  Click in B2 to check the formula. Formula can contain values, cell references, mathematical operators and functions.

Read More