CGS2060/2100 Recitation Spreadsheets

1. CGS2060/2100 RecitationSpreadsheets

2. Spreadsheet Software • Software designed to perform complicated numeric calculations rapidly and accurately. • Provides tools that assist in understanding the relationship that exists between numeric data such as cost and profit. • Ideal for examining “What If” scenarios.

3. A Spreadsheet • A spreadsheet consists of a grid of “cells” that can contain text, number (values), equations, or functions. 3. Values are used in equations and functions 4. Equations and functions operate on values 2. Text is used for labels 1. Cells are identified by column letter and row number. This is cell E3.

4. A Spreadsheet • Column width, row height, font and cell format can all be adjusted using commands in the format menu. 2. Cell fill color and border color and weight can be specified. 3. Numbers can be formatted as currency, percentages, dates, etc. 1. Font type, color, size and weight can be changed

5. Formulas (equations) • Calculations always begin with an equal sign followed by the formula or function. • After typing the formula, pressing Enter displays the results of the formula in the cell – the formula remains displayed in the formula bar. 2. Cell E3 shows the results of equation 1. The formula bar shows the equation typed in cell E3

6. Formulas • What formula would you type in cell B6 to add up the January profits for the three divisions? B3+B4+B5 = It’s common for new users to forget to type the equal sign for formulas. = B3+B4+B5

7. Functions • What if you have a long list to add up? • Rather than having to type lengthy formulas, Excel provides functions. • In this case you could use =sum(B3:B17) Functions provide shorthandmethods for specifyinglengthy or complicated formulas and calculations The colon is used to specify a range of cells, B3 through B17

8. Functions • Fortunately, you don’t need to memorize all the functions to use them. All you need to so is type the equal sign in a cell to access the functions menu. • Click the Dropdown Arrow to access the list of functions.

9. Functions • Fortunately, you don’t need to memorize functions to use them. All you need to so is type the equal sign in a cell to access the functions menu. • Click the Dropdown Arrow to access the list of functions. • Click the desired function.

10. Functions • A dialog box is displayed to assist you in setting up the function. Enter cells, or cell range here (or select them with your mouse) Click OK to insert the function in the spreadsheet

11. Excel provides hundreds of functions for many categories of problems Financialrate() Statisticalgrowth() Math and Trigsqrt() Logicif() Date and Timenow() Common functions that we’ll use in this class include Sum=sum(A1:A12) Average=average(A1:A12) Minimum=min(A1:A12) Max=max(A1:A12) If=if(A1>12, “okay”) Functions

12. Copying Functions and Equations • If you copy a function from one cell to another, the cells referenced in the function will automatically change to accommodate the new location. This is called a relative reference. For instance, below =SUM(B3:B5) becomes =SUM(C3:C5) when copied.

13. Copying Functions and Equations • By placing a dollar sign before the column letter or row number in a formula you can prevent the cell reference from changing when copied. This is called an absolute reference. Below, B3, the interest amount, has been made an absolute reference.

14. Copying Functions and Equations • You can copy and paste a function using commands in the edit menu. • If you wish to paste a function several times in sequence, you can use the cell’s fill handle to drag and copy a formula or equation down, up, right, or left. Fillhandle

15. What if…? • The purpose of spreadsheets is to provide users with insight and an understanding of complicated numerical data and the relationships between that data. What if the SouthEast division earned $20,000 in profits in January? A change here.

16. What if…? • The purpose of spreadsheets is to provide users with insight and an understanding of complicated numerical data and the relationships between that data. What if the SouthEast division earned $20,000 in profits in January? Effects the data here, here & here

17. What if…? • Excel provides a number of “what if” tools. The “solver” tool for example allows you to work backwards to find out what needs to be done to meet a stated goal. Here the solver is asked: What monthly profits must each of the divisions earn for us to meet a goal of $200,000 in total profits.

18. Another “What If” Scenario • In this example, Sharon needs $40 on Saturday to be able to join her friends for a weekend trip. Her current budget leaves her with -$9.75. She could use this sheet to tweak her spending during the week so that she would have money left over for the weekend, or use a tool like the Solver to assist in finding areas to reduce her spending.

19. Other Cool Stuff: Charts • Select a range of cells and click the Chart Wizard button to create a bar, line, or pie chart.

20. Other Cool Stuff: Pivot Tables • Using the Pivot Table Wizard you can study the data in a spreadsheet from different organizational views.

21. Summary • This short presentation has introduced you to basic Excel concepts and features: • Cells, labels, and values • Formatting text and cells • Changing row height and column width • Formulas and functions • Relative and absolute cell references • Copying functions • “What if” tools • Charts and Pivot Tables