G042 - Lecture 14

Excel Functions and Formula

Mr C JohnstonICT Teacher

www.computechedu.co.uk

Session Objectives

Understand how to use a range of different functions in excel.

Used a range of functions within your excel spreadsheet.

Sum Function

A 1 25 2 50 3 75 4 test 5

Example Cells to ADD Answer

=sum(A1:A3) A1, A2, A3 150

=sum(A1+A4) A1, A4 #VALUE!

=sum(A1:A2, A5) A1, A2, A5 75

The syntax for sum function=Sum(first cell:last cell)

Max or Min Function

A 1 25 2 50 3 75 4 13 5 36

Example Cells look at Answer

=max(A1:A5) A1, A2, A3, A4, A5 13

=mix(A1:A5) A1, A2, A3, A4, A5 75

The syntax for max function=Max(first cell:last cell)

The syntax for max function=Min(first cell:last cell)

Average (Mean) Function

The syntax for average function =Average (first value, second value,

etc.)

A 1 25 2 50 3 75 4 100 5

Example Cells to average Answer

=average(A1:A4) A1, A2, A3, A4 62.5

=average(A1:A4, 300)

A1, A2, A3, A4 and 300 100

=average(A1:A5) A1, A2, A3, A4, A5 62.5

=average(A1:A2, A4)

A1, A2, A4 58.3

Other Averages

To work out the median =Median (first value, second value,

etc.) To work out the mode

=Mode (first value, second value, etc.)

A 1 36 2 50 3 75 4 13 5 36

Example Cells look at Answer

=mode(A1:A5) A1, A2, A3, A4, A5 36

=median(A1:A5) A1, A2, A3, A4, A5 36

Count Function

A 1 10 2 20 3 30 4 Test 5

Example of Count Cells to look at Answer

=Count (A1:A3) A1, A2, A3 3

=Count (A1, A3) A1, A3, 2

=Count (A1, A4) A1, A4 1

=Count (A1, A5) A1, A5 1

The syntax for sum function=Count(first cell:last cell)

Blank entries are not counted. Text entries are NOT counted.

CountA Function A

1 10 2 20 3 30 4 Test 5

Example of CountA Cells to look at Answer

=CountA (A1:A3) A1, A2, A3 3

=CountA (A1, A3) A1, A3, 2

=CountA (A1, A4) A1, A4 2

=CountA (A1, A5) A1, A5 1

The syntax for sum function=CountA(first cell:last cell)

Blank entries are not counted. Text entries are counted.

IF Function

A B

1 Price Over a Pound?

2 £0.95 No

3 £1.37 Yes

4 Comparing # Returning #

5 14000 0.08

6 8453 0.05

Example of IF typed into Column B

Cells to look at Answer

=IF (A2>1,"Yes","No") is ( .95 > 1) No

=IF (A3>1,"Yes","No") is ( 1.37 > 1) Yes

=IF (A5>10000, .08, .05) is (14000 > 10000) .08

=IF (A6>10000, .08, .05) is (8453 > 10000) .05

The syntax is =IF (condition, value-if-true, value-if-false)

value returned may be either a number or text if value returned is text, it must be in quotes

CountIf Function The syntax for sum function

=CountIf(first cell:last cell, criteria)

Example of CountA What looking for Answer

=COUNTIF(A2:A5,"apples") Number of cells with apples in the first column above

2

=COUNTIF(A2:A5,A4) Number of cells with peaches in the first column above

1

=COUNTIF(A2:A5,A3)+COUNTIF(A2:A5,A2)

Number of cells with oranges or apples in the first column above

3

=COUNTIF(B2:B5,">55") Number of cells with a value greater than 55 in the second column above

2

=COUNTIF(B2:B5,"<>"&B4)

Number of cells with a value not equal to 75 in the second column above

2

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")

Number of cells with a value greater than or equal to 32 and less than or equal to 85 in the second column above

3

A B 1 Data Data 2 Apples 32 3 Oranges 54 4 Peaches 75 5 Apples 86

Int Function

A 1 2 19.5 3 4 5

Example Description Answer

=Int(-8.9) Rounds down -9

=Int(8.9) Rounds down 8

=Int(A2) Rounds down 19.5 19

=A2-Int(A2) Returns the decimal

0.5

Int returns the nearest integer The syntax for Int function

=Int(number)

LOOKUP Functions

Returns a value from a list The syntax for VLOOKUP function

=VLOOKUP(lookup_value, table_array, column where return data is, false)Example Description Answe

r

= VLOOKUP("DI-328", A2:D6, 3, FALSE)

Returns the cost price of diapers $21.45

= (VLOOKUP("WI-989", A2:D6, 3, FALSE)

Returns the cost price of wipes $5.12

= VLOOKUP(“BI-567", A2:D6, 3, FALSE)* (1+(VLOOKUP(“BI-567", A2:D6, 4, FALSE)))

Returns the sale price of bib by finding the cost and multiplying by the mark up

$4.98

Function Wizard

In excel there is a help tool for functions called the function wizard.

There are two ways to get the the function wizard. If you look at the standard toolbar, the function wizard icon look like the icon on the right.

The other way to get to the function wizard is to go to the menu INSERT -- down to FUNCTION.

Next Steps

Add a range of useful functions to your spreadsheet – think why you are going to include them – don’t just include for the sake of it.

Help Sources

Online tutorials Help In Excel Vlookup tutorial on my site