Microsoft Excel Functions

Microsoft Excel Functions Begin with an equal sign ( = ) so that Excel can recognise that what you are entering is a formula. Ex: =(4 + 7) 1.

Excel’s arithmetic operators Operator

Name

+

plus sign

Addition

-

minus sign

Subtraction (or to indicate a negative number)

*

asterisk

Multiplication

/

slash

Division

%

percent sign

Percent

^

caret sign

Exponentiation ( " to the power of ")

Examples Formula

Result

=(2+4)

6

=(8*3)

24

=35%

0.35

=(14/7)

2

=(8^2)

64

2.

Description

Excel’s logical operators Symbol

Meaning

=

Equals

<

Less than

>

greater than

<=

less than or equal to

>=

greater than or equal to

<> 3.

not equal to

Evaluating logical expressions

Excel can evaluate whether certain mathematical statements are true or false. If you enter =(56 < 57) in a cell, Excel returns TRUE because 56 is less than 57. If you enter =(58 < 57) in a cell, Excel returns FALSE because 58 in not less than 57. 4.

Σ

Using AutoSum The AutoSum tool is very convenient and is probably one of the most useful functions available in Excel.

Figure 1 shows a column of figures in the range B1:B4 (B1 to B4). If you highlight B5 and click the AutoSum button (Σ), the sum of all the cells appears in cell B5. You can use the AutoSum tool in the same way when all the data is displayed in a row. Just highlight the first empty cell in the row and click the AutoSum button. The sum of all the preceding cells is displayed in the last cell. CITY

SALES

1 Johannesburg

R 500,000.00

2 Pretoria

R 120,000.00

Symbol 3 Cape Town

R 25,000.00

##### Total

#DIV/0! #N/A

#NAME?

Figure 1 5.

Error messages

Meaning

TheRcell is too narrow for the value produced by the formula. 645,000.00 Resize the cell as necessary. In one way or another you have tried to divide by zero. Make sure that the divisor is not zero. One of the values needed to complete the formula is not available. You have used a range name in your formula that Excel does not recognise. If you have misspelled the name or referred to a range with a name you forgot to assign to the range, the formula will not work.

6.

Selected functions

6.1

Average(Number1,number2,….) Returns the average of the given range of numbers. Average(7,9) = 8 , because (7+8)/2 = 8

6.2

Even (number) Rounds a number up to the nearest even integer. Negative numbers are adjusted away from zero. Even(7.4) = 8, Even( -7.4) = -8.

6.3

Max (number1,number2,..) Returns the largest value in a set of values. Max(8,2,9,734) = 734.

6.4

Min (number1, number2,..) Returns the smallest value in a set of values. Min (8,2,9,734) = 2.

6.5

Sum(number1,number2,…) Adds all the numbers in a range of cells. Sum(3,4,7) = 14 , because 3 + 4 + 7 = 14.

6.6

Today( ) Returns the current date.

7.

More functions Go to the symbol fx and the select the required function.

8.

Relative references and absolute references

8.1

Relative references Relative references update when you copy or move formulas. If you copy =C6/C7 one cell to the right, the new formula becomes = D6/D7.

8.2

Absolute references are indicated by the dollar $ sign preceding the row or column designation that is absolute. They do not update when copied or moved. They reference the same cell address, no matter where the formula is in the worksheet. If you copy the formula =C10/$B$9 one cell to the right, the new formula becomes =D10/$B$9. Only the relative part of the formula adjusts; the absolute reference remains fixed on a specific cell address.