Formulae and Functions

          Formula are users made (it is defined by a user’s without excel instructions) When a user uses any kind of

logic to get an answer is called formula. 

Using Formulae

Simple mathematical operators can be used to:

Add (+)  Also known as SUM

Subtract (-)

Divide (/)

Multiply (*)

Calculate Indices (powers) of a number 

AdditionCalculation:

If B2 (20), B3(40)

Move the cursor into cell B4. You need to insert formula that adds(sums) the

contents of cell B1 and cell B2.

Enter the following formula: =B1+B2 

Hit the <Enter> key to set the formula 

Your answer will be (60)

Subtraction Calculation:

If C2 (100), C3(50)

Move the cursor into cell C4. You need to insert formula that calculates the difference

between the two numbers. (Difference being number 1 minus number 2)

Enter the following formula: =C2-C3

Hit the <Enter> key to set the formula.

Your answer will be (50)

Multiplication Calculation:

If D2(5), D3(5)

Move the cursor into cell D4. You need to insert formula that calculates the product

of the two numbers. (Product means to multiply number 1 and number 2)

Enter the following formula: =D2*D3

Hit the <Enter> key to set the formul

Your answer will be (25)

Functions

Function is built set of instructions. (In functions excel gives a set of instructions to arrive at the answer)

If A2(10) AND A3(40)  = sum(A2:A3) 

Your answer will be (50)

Breakdown of the formula

= SUM (B4:B8)

Ways of using the SUM function

There are many ways of using the SUM function. Some of these ways are highlighted

in the table below:

Function Equivalent Formula What it does

=SUM(B4:B8) =B4+B5+B6+B7+B8 Adds up the contents the contents of all the

cells in the range B4 to B8

AVERAGE Function

If B4(10), B5(5), B6(8), B7(12), B8(20)

To find the AVERAGE number of B4 TO B8, click

into cell B10.

Enter the formula =AVERAGE(B4:B8).

This should give the value 11.

MAX Function

If C4(16), C5(14), C6(8), C7(12), C8(20)

To find the MAX number click into cell C10.

Enter the formula =MAX(C4:C8).

This should give the value 20

MIN Function

If D4(4), D5(2), D6(8), D7(10), D8(15)

To find the MIN number click into cell D10

Enter the formula =MIN(D4:D8).

This should give the value 2

SUMIF Function

SUMIF compares each value in a range of cells and if the value matches the given

condition it will add another related cell to give a running total.

To find the sumif click into cell F6

=sumif(F2:F5,”>100″) This  should give value 350