Introduction to Spreadsheets Fundamental Skills 1 Spreadsheets A

Introduction to Spreadsheets Fundamental Skills 1

Spreadsheets • A spreadsheet is an application that is used for performing calculations. • Spreadsheets consist of columns and rows. 2

Spreadsheets • The intersection of each row and column is called a CELL • Each cell is identified by a row and column reference such as A 4, D 6 etc. 3

Cells • Cells can contain either text, numbers or formulas. • To enter data in the cell it must first be selected (by clicking on the cell) 4

• Cells can be formatted to display numbers as either date, currency, percent, decimal and other numerical formats. • Most common text formatting options are also available 5

Formulas • Formulas are used to make a spreadsheet more versatile. • When writing formulas in a cell an equal sign “=“ is first entered. Mathematical Operations Multiplication * Division / Addition + Subtraction - Relational Operations Equal = Not equal to <> Less than < Greater than > Less than or equal to <= Greater than or equal to >= 6

Formulas • Formulas should be written in terms of cell references e. g. = C 4 + C 5 + C 6 =C 4+C 5+C 6 7

Formulas • Once the formula is entered into the cell the results will be shown. 8

Functions • There are many formulas or FUNCTIONS that can be used in a spreadsheet. Functions make calculations easier. • These functions cover a variety of categories such as mathematics, financial or logical. 9

Functions • Common functions include SUM, AVERAGE, MAX and MIN. • Functions consist of a name, a set of brackets and arguments or parameters. • Arguments are the values on which the functions operate. 10

SUM • The SUM function is one of the most commonly used. It adds up all the numbers in a range of cells. =sum(A 1, A 2, A 3, A 4) adds cells A 1, A 2, A 3 and A 4 =sum(A 1: B 5) =sum(A 1, B 6, C 67, F 2) adds the cells indicated. adds all the numbers from A 1 to B 5 11

=sum(C 4: C 6) 12

AVERAGE • The AVERAGE function operates in a similar way as SUM. =AVERAGE(A 1, A 2, A 3, A 4) averages cells A 1, A 2, A 3 and A 4 =AVERAGE(A 1, B 6, C 67, F 2) averages the cells indicated. =AVERAGE(A 1: B 5) averages all the numbers from A 1 to B 5 13

=average(D 4: D 6) 14

MAX and MIN • MAX returns the largest value from a range of cells. =MIN(B 2: B 9) =MAX(B 2: B 9) • MIN returns the smallest value from a range of cells. 15

Highest Profit =MAX(D 4: D 6) Lowest Profit =MIN(D 4: D 6) Highest Profit Lowest Profit 16

Repetitive Formulas • Often the same formula will need to be applied to different ranges of cells. 17

Repetitive Formulas • Instead of entering the same formula in each cell and adjusting the cell reference, it is possible to ‘fill’ the formula across and have the cell referencing adjusted automatically. • First select the cell with the formula and then either select FILL from the edit menu or use the ‘FILL’ handle. 18

Repetitive Formulas =sum(C 4: C 6) =sum(D 4: D 6) =sum(E 4: E 6) FILL handle Note: Fill Down may also be used to copy formulas down a column/s 19

Absolute References • When ABSOLUTE references are used the contents of the formula are not changed when filling down or across. – i. e. the cell references remain the same. • Absolute references are indicated by placing a ‘$’ before the column and/or row reference. – e. g. $A$1 20

Absolute References =C 4*$F$3 =C 5*$F$3 =C 6*$F$3 =C 7*$F$3 21

Supporters We would like to express our thanks for the continued support from: The Australian Newspaper and Academic Assessment Items 22