CS 101 lecture exam 1- Spreadsheet Basics

Freezing rows and columns

- process of keeping headings on the screen at all times • Allows you to work easier with large worksheets • Can freeze both rows and columns or just the top row or first column

Using Autosum

• Automates sum function • Click cell where you want the function • Click autosum button (sigma) • Select range of cells you want to sum • Press enter • =SUM(C4:C10) represents the sum of all the cells in the cell range C4 to C10 -MISTAKE: =sum(a4+b4+c4)

How to select a range

-Click and hold left mouse button and drag from beginning of range to end -Select first cell, then hold the Shift key while clicking the last cell

Creating and editing a worksheet

-Data is entered into cells where rows and columns intersect. -Rows are Numbered, Columns Assigned Letters -Cells identified by their cell address, such as A1 or E5. -Can refer to or select a range of cells (A1:E1) - range in a row (A1:A10) - range in a column (A1:E10) - range in a rectangle

Using Spreadsheets Across Disciplines

-Used for business applications, such as accounting -Used for "what-if" analysis in business planning -Can also be used in scientific applications -Geologists can use to chart data about scientific phenomena -Social Scientists can use to predict voting results

Spreadsheet software

-uses rows and columns of numbers to create a model or representation of a real solution -Microsoft Excel, provides tools for creating worksheets

noncontiguous range

2+ separate, nonadjacent ranges

Spreadsheet

a document containing values and other data organized into rows and columns; created with spreadsheet software. Microsoft Excel Corel Quattro Pro Lotus 1-2-3

range

a rectangular group of cells in a worksheet Can be one cell; may be entire worksheet

Absolute cell references

are exact. DO NOT CHANGE when a formula is copied • Indicated by "$" in front of the column letter and row number • Used when the value need not change • Use F4 to toggle between relative, absolute, and mixed cell referencing o Copying formulas

contiguous range

cells are all grouped together

Relative cell references

change relative to the direction in which the formula is copied • When you copy a formula containing a relative cell reference over multiple columns, the column letter changes • When you copy a formula containing relative cell reference down multiple rows, the row number changes

spreadsheet origins- Visicalc

first spreadsheet program for microcomputers • Dan Bricklin and Bob Frankston 1978-1979 • Written for Apple II PCs -"killer app" for microcomputers

The cells within a range are specified by?

indicating the diagonally opposite corners, typically the upper-left and lower-right corners of the rectangle. For example, B4:I4. is a range of cells starting with cell B4 and ending with cell I4.

Displaying cell references

press ctrl + ~

Error messages

• !DIV0→ divide by zero • Cell full of #'s→ cell too narrow for contents, widen columns • #NUM→ invalid argument value for function, like SQRT(-1) • #NAME→ invalid name for function • Circular references

edit data in a cell

• 3 ways • Select the cell to edit, click in formula box to make changes, press enter • Double-click in cell to be edited, make changes, press enter • Select cell, press F2, make changes, press enter

Cell contents

• Can contain one of 3 things • Label- descriptive text • Constant value- number that is typed in • Formula- derived value calculated by the machine o Can contain values, arithmetic symbols, parenthesis, functions, and cell references

clear contents in a cell

• Click cell then delete • Click clear arrow on editing group on the home tab

Autofill

• Copy the content of a cell or range of cells • Fill handle: is a small black square appearing in the bottom-right corner of a cell -Use to copy contents of one cell many times -Use to complete a sequence like years or months

Date Functions

• Handle time-based calculations • Help analyze data relating to time • TODAY() places current date in the selected cell only • Updates when the file is opened again • NOW() displays current date and time side by side

Sorting data

• Make sure you select all data to be sorted not jus the column that provides the order • Data menu then sort • Header row=first row in selection used as labels and not moved when the sorting happens • Ascending= a-z order, descending= z-a order

What problems are suitable for spreadsheets?

• Moderate amount of data that will be reused • Need for formatting, graphing, analysis • No large amounts of text • Not large amounts of data or complex queries

Basic Statistical Formulas

• Perform a variety of calculations to aid in the decision making process • AVERAGE calculates the average of a range of numbers • MIN calculates the minimum value of a range • MAX calculates the maximum value of a range • COUNT counts the number of values within a range • MEDIAN finds the midpoint value in a range

What happens if a formula is copied?

• Relative cell reference→ no "$" used, (B7/3.0) o Cell reference changed in new location • If copied to C9 formula would change to (C9/3.0) automatically • Absolute cell reference→ uses one or more $, ($B$7/3.0) o Cell reference not changed in new location o "Anchored" so that it always refers to a specific cell

How to freeze columns and rows

• Select cell below the row(s) and to the right of the column(s) to be frozen • Click view tab and click freeze panes in the window group

formulas

• Tells computer to do calculations • Most use values in one or more cells to do the calculations • Uses cell reference to tell the formula what cells to use in the calculation instead of the actual data • Start with =,+, or ( • Uses algebraic hierarchy of operations • ( ), Fx, * and /, then + and - • Evaluated by computer and answer displayed in appropriate cell • Automatic recalculation

Printing a worksheet

• With or without formulas shown • Custom printing areas • Always use print preview