8 01 SPREADSHEETS AND COMPONENTS OF SPREADSHEETS 2

8. 01 SPREADSHEETS AND COMPONENTS OF SPREADSHEETS

2 PART A - Spreadsheet Basics

What is a Spreadsheet 3 Spreadsheet/Worksheet is an arrangement of cells in columns and rows. Used to organize, analyze, calculate, and report information, usually in numerical form. A workbook is a file which contains one or more spreadsheets.

Uses of a Spreadsheet 4 Daily Uses of Spreadsheets: � Balancing a checkbook � Calculating car loans � Calculating student grades � Household budgets Do you or your family use spreadsheets? Why would a business use spreadsheets? � Payroll � Financial statements for a business (profit/loss)

What-if Analysis A spreadsheet can be a tool used to play out different situations to determine the outcome. An advantage of a Spreadsheet is that it answers “What If” questions.

Parts of a Spreadsheet 6 Cell – intersection of a row and column Column— identified by letters of the alphabet (vertical ) Example “Column A” � Row— identified by numbers (horizontal)Example COLUMNS (vertical) “Row 3” A ROWS (horizontal) 1 2 3 B CELL C D

Cell Specifics 7 Cell Range: • A Range is a group/block of cells. • Example: A 6: E 16 (Range of cells in a spreadsheet). Cell Address/Cell Reference: a specific location • It is the Column letter and Row number. • Example Cell A 4 Active cell: • The cell that is selected with a dark boarder. • It is the cell that is ready to receive data.

8 3 Components of a Spreadsheet Cell Data Formulas/Functions Operations

Cell Data -- is classified according to its intended purpose. 4 Types of cell data � Labels � Values � Formulas � Functions

LABELS 10 Labels —Alphabetical text or numbers that will not be used in Left Aligned calculations. Examples: John Jones (text) Dates, such as 1/03/2009 (considered as text) Social Security # Phone # ZIP Code (a number, but will not be calculated) Using an apostrophe ‘ Type an apostrophe (‘) before a number to make that entry recognized as a label (the ‘ does not show when you press enter). Example: ‘ 27613 (Put ‘ so the ZIP Code is recognized as a label or text, not a value, by the computer)

Vaules 11 Values —Data that be used in calculations. Example: 150 Values –Right Aligned

12 PART B - Formulas and Functions

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

Basic Spreadsheet Formulas 14 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 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 Formula Label Results of the Formula (=G 2+G 3+G 4+G 5) Values

Order of Operations 17 Order of Operations-Calculations are performed in a specific order. (Excuse My Dear Aunt Sally) P E M D A S parentheses parenthesis first exponentiation multiplication division addition subtraction performs operations on the items enclosed in the ^ * / + - (from left to right) Example =(A 8+C 9)/(H 8 -L 9) Excel will calculate A 8 = 10 =(10+2)/(7 -1) 1. Formula in parentheses(A 8+C 9)C 9 = 2 SOLVE!! =12/6 2. Calculate (H 8 -L 9) H 8 = 7 =2 3. Divide the 2 results. L 9 = 1

Order of Operation Example 18 =(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 19 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 Function Name AVERAGE of cells Description Determines the average of the range. MAX cells. Finds the highest number in the range of MIN cells. Finds the lowest number in a range of SUM Adds the total of range of cells.

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

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

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

Absolute Cell 24 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 25 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.