WorkSheets - Computational Methods - Lecture Slides, Slides of Computational Methods

Download WorkSheets - Computational Methods - Lecture Slides and more Slides Computational Methods in PDF only on Docsity!

MS Excel

WorkSheets

Learning Goals

• Enter Data into Excel
  • Use Copy and Fill
• Perform Calculations Using Excel
  • Writing “Formulas”
    • Proper Syntax
    • InterRelationship Between Cells
  • Invoking and Using Built-in Functions
• Explain the difference between ABSOLUTE

and RELATIVE addresses

SpreadSheets

• SpreadSheet Defined ≡

A table of values arranged in rows and

columns. The Intersection of a Row &

Column is Called a Cell.

• Each cell-value can have a predefined relationship to the other cell values.
• If you change one cell-value other values may change depending on the interrelationship of the cells.

MicroSoft Excel

• Excel is the MS-Office Application program used to create spreadsheets
• Within Excel, users can organize data, create charts, and perform calculations.
• Excel allows the user to create very large spreadsheets, reference information from other spreadsheets, and it permits organized storage and modification of information
• Excel operates like other MS Office programs and has many of the same functions and shortcuts as MSword & MSpowerpoint

MSExcel 2010

Specifications

http://office.microsoft.com/ en-us/excel-help/excel- specifications-and-limits- HP010342495.aspx?CTT =5&origin=HP

17e cells/wksht

Excel WorkSheet (1)

 An Excel File is also Called a WORKBOOK

• Each workBOOK can Contain a large number of workSHEETs (>200)  Where a column and a row intersect is called a CELL.
• For example, cell B5 is located where column B and row 5 meet. You enter your data into the cells on the Active (Displayed) worksheet  The tabs at the bottom of the screen represent different worksheets within a workbook.

Selected Cell

NAME BOX: Displays Selected Cell

Active WorkSheet

WorkSheet ScrollButtons

Excel Data Types

 Spreadsheets can contain Several types of data

• Numbers
• Text
• Dates & Time
  • Can Add, Subtract
• Currency
• Charts
• Equations
• Pictures

Cell Addressing

 COLUMNS are designated by alphabetical values such as A, B, C ….  ROWS are designated by numerical values such as 1, 2, 3 ….  Individual cells are designated by ordered pairs containing the row and column designation such as A1,C7, B10 ….

B10 =Active Cell

Excel FORMULA Entry

 To Write a Formula, Rt-Clik the Cell To make it Active

• Start Typing, OR
• Type in Formula Bar

 You can also calculate values using formulas.

 The formula can perform mathematical operations using data from other cells.

Fill FORMULAS

 Enter the Formula in One Cell  Select the Formula Cell, Bottom Rt Corner of selection Box turns to a “+”  Drag the the + up/down or Across  “Cell References” adjust automatically

Anatomy of a Formula

RELATIVE Reference; Changes when cell Filled, Copied or Moved

xB (^) 4 = xC 4 { X^ F 17 (^ X F 23 − xC 4 ) XF 16 }

ABSOLUTE Reference; Does NOT Change when cell Filled, Copied or Moved

MIXED Reference; COL (F) Changes, while ROW (16) Does NOT when moved or copied

Excel Built In Functions

• SUM (math & trig)
• AVERAGE (statistical)
• SIN (math & trig)
• IF (logical)
• NPV (financial)
• CONCATENATE (text)
• LOOKUP (lookup and reference)
• Many More …

AVERAGE(number 1, number 2,…)

• Example

REGION A1:E1 is NAMED “Luminance”

 Then Several Average Calcs

SIN(number)

• NOTE: Angle (number)

must be provided in radians

• If The argument is in degrees (°), Use the RADIANS Fcn to convert it to radians.

 Some SIN & COS Calcs

≈ 0. 0

sin ( 30 °) =