1 8 01 SPREADSHEETS AND COMPONENTS OF SPREADSHEETS

1 8. 01 SPREADSHEETS AND COMPONENTS OF SPREADSHEETS

2 PART B - Formulas and Functions

Formulas 3 Formula –A Statement that instruct the software to perform a calculation. Begins with an equal sign (=) The equal sign (=) lets the software knows that the data will be used in a calculation.

Basic Spreadsheet Formulas 4 Formulas use the following math operators: + plus for Addition - hypen for Subtraction * asterisk for Multiplication / diagonal for Division Examples of basic formulas: if using Cells B 7 and C 7: Addition Subtraction Multiplication Division =B 7+C 7 =B 7 -C 7 =B 7*C 7 =B 7/C 7

Parts of a Spreadsheet Formula 5 mathematical operators =B 2+C 2+D 2 equal sign—first part of any spreadsheet formula cell reference/address—made up of the column heading and the row number

Identifying Labels, Values and Functions 6 Formula Label Results of the Formula (=G 2+G 3+G 4+G 5) Values

Order of Operations 7 Order of Operations-Calculations are performed in a specific order. (Please Excuse My Dear Aunt Sally) P E M D A S parentheses items parenthesis first exponentiation multiplication division addition subtraction performs operations on the enclosed in the ^ * (from left to right) / + - (from left to right)

Order of Operation Example 8 =(A 8+C 9)/(H 8 -L 9) Excel will calculate 1. 2. 3. Formula in parentheses(A 8+C 9) Calculate (H 8 -L 9) Divide the 2 results. A 8 = 10 C 9 = 2 H 8 = 7 L 9 = 1 SOLVE!! =(10+2)/(7 -1) =12/6 =2

Functions 9 Function-A mathematical operation built into a spreadsheet program to perform a shortcut for common calculations. Like formulas, functions instruct the software to perform a calculation. Functions also begin with an equal = sign

Spreadsheet Functions 10 Function Name AVERAGE COUNT range Description Determines the average of a data range Counts the number cells in a data MAX Finds the highest number in a data range MIN Finds the lowest number in a data range ROUND decimal Rounds the value of a cell to a specific placement SUM Totals or adds the values in a data range

Parts of a Spreadsheet Function 11 =SUM(B 2: B 6) name of function equal sign range

Identifying Functions 12 function The result of the function =SUM(B 2: B 5)

Relative Cells 13 Identifies the location of a cell or group of cells As a formula or function is copied and pasted to other cells, the cell reference changes to reflect the function's new location.

Absolute Cells 14 Consists of the column letter and row number surrounded by dollar signs $. � Example: $C$4, or $G$15. Use when you want a cell reference to stay fixed on a specific cell. As a formula or function is copied and pasted to other cells, the cell references in the formula or function do not change

Mixed Cell 15 A combination of relative and absolute cell references. The dollar sign ( $ ) is used in mixed cell references to indicate that a column letter or row number is to remain fixed when a copied from one cell to another. � Examples: For $E 4 or F$6. $E 4, the column letter is fixed and the row number is allowed to change when copied to other cells. For F$6, the row number is fixed while the column letter changes.