1 / 23

Spreadsheets

Spreadsheets. First "programming" example in this course. You are telling the computer what you want it to do, with a series of commands. The commands are implied, but are none the less a set of instructions. Spreadsheet Basics.

nishi
Download Presentation

Spreadsheets

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Spreadsheets • First "programming" example in this course. • You are telling the computer what you want it to do, with a series of commands. The commands are implied, but are none the less a set of instructions.

  2. Spreadsheet Basics • Derived from computerized, real-time responsive form of the accounting ledger. Its applications have moved far beyond accounting applications. • Widely used for analysis, presentation and plotting of sets of many variables.

  3. Spreadsheet Format • The data is arranged in rows and columns. • A “row” is horizontal; numbered top to bottom • A “column” is vertical; identified by letters of the alphabet ordered left to right

  4. • Intersection of a row and a column defines a "cell". A cell can hold text, numbers, or things calculated using a formula. • Cells are named by their locations. • A1 is cell in leftmost column (A) and topmost row (1) • D5 is cell in column D and row 5 • Always give column first, then row • Addresses are not case sensitive (cell A1 and cell a1 refer to the same cell)

  5. Spreadsheet Elements • Data is presented in worksheet windows. These windows are bordered by row and column labels • "Tabs" are used in Excel for multiple worksheets in the same file. These can be seen at the bottom of the spreadsheet area.

  6. Cell Contents Numbers These numbers can have adjustable precision and a variety of styles (e.g. Scientific, Dates, Currency). 3.14159 $2.59 1,000,000 24% 6.23e-10 12:00 2/28/2009

  7. Cell Contents • Text (Labels) • These text entries can also be formatted in most modern spreadsheets. Feet Barr ECE 1331 “1234”

  8. Cell Contents • Formulas are expressions which include numbers, operators, defined mathematical and other functions, and/or the values obtained from other cells. • In Excel, formulas must begin with an equal sign • Using formulas provides one of the great powers of the spreadsheet. The key to this power is to know how to address the cell desired.

  9. Mathematical Operators • Addition (+): 5+2 • Subtraction (-): 7-3 • Multiplication (*): 2*A (NOT 2A) • Division (/) 4/3 (NOT ) • Exponent (^) 5^3 (NOT 53)

  10. PrecedenceOrder of evaluation when several operators appear in a single expression • Parenthesis • Function evaluation • Exponentiation • Multiplication & Division (left to right) • Addition & Subtraction (left to right)

  11. Precedence Examples =(3*(a+1))/(d-2) =25^(1/3)

  12. Intrinsic Functions • There are many predefined functions in Excel. A few examples are: • AVG, SUM, MIN, ABS,SQRT • COS, SIN, LN, EXP • IF,ISBLANK,ISTEXT • Refer to Excel Reference Sheet in Course Guide—Given on exams; bring to class for quizzes!

  13. Cell Addressing in Formulas • Relative Addressing - the addressed cell is with respect to where the cell containing the formula is. • =A3+B4 entered in cell A4 means add value in cell above current cell to value to the right of current cell so when copied to cell C5 becomes = C4+D5

  14. . Cell Addressing in Formulas • Absolute addressing - the addressed cell is with respect to the spreadsheet • In Excel, precede column and/or row with $ sign: • $B2 means col B is “absolute” but row 2 is “relative” • C$3 means col C is “relative” but row 3 is “absolute” • $A$1 means both col A & row 1 are “absolute”

  15. Example =C1+C2+C3 Copy to C4

  16. Example =$A1+$A2+C3 Copy to C4

  17. Ranges • A rectangular range of cells can be indicated by giving the address of the upper leftmost cell followed by a colon followed by the address of the lower rightmost cell • =sum(a3:a6) is the same as =a3+a4+a5+a6 • =max(b2:d2) is the same as =max(b2,c2,d2) • =min(b3:c4) is the same as =min(b3,b4,c3,c4)

  18. Named Cells • Rather than always referring to a cell by its address, you can define a more meaningful name, making formulas more readable • For example, if cell C3 contains the value of the cost per unit, and cell D5 contains the number of units, then =cpu*num would be more meaningful than =C3*D5

  19. Named Cells • By default, named cells are always absolute • The formula cpu*num would actually be = $C$3*$D$5 • Names are not case sensitive • Must begin with letter or underscore • Must not conflict with built-in names

  20. Named Cells If we are entering a student’s 33 homework grades in cells B2 through B34, then we could enter in cell B50, =average(B2:B34) or, if we name the range B2 thru B34 as HW, use the formula =average(HW)

  21. Data Manipulation • We have a variety of ways of manipulating data in a spreadsheet. These methods include: • Data Fill - where the same kinds of data or formulas are used to fill up a range of cells • Sorting - where groups of data are arranged in order, usually by row or by column

  22. Graphing • While not originally a spreadsheet function, plotting of data has become an important feature of all spreadsheet programs Excel calls them “charts”! • Types include column, line, pie. scatter and many, many more • Note: The “usual” plot of a mathematical function is a scatter type—NOT a line!

  23. Data Import/Export • It is possible to bring data into a spreadsheet (import) and produce files for use of other applications (export). • Class rolls are now being made available to faculty as “comma separated value” files, suitable for import into Excel • Excel can save a sheet in “space delimited text” form, suitable for use by a C or Fortran or Matlab program

More Related