Introduction to Spreadsheets

• EECS 1520 at York University covers the fundamentals of spreadsheets.

Spreadsheet Applications

• Definition: A spreadsheet is a computer application or program that enables the user to tabulate and collate data.

• Uses:

  • Data can be used to make calculations.

  • Data can be presented using graphical representations or analysis.

  • A spreadsheet consists of a grid of 'cells' arranged in rows and columns, where data can be inserted into each cell.

• Common Spreadsheet Applications:

  • VisiCalc

  • Lotus 123

  • Microsoft Excel (used in this course)

  • Google Sheets

  • Apple Numbers

Why Spreadsheets are Important

• Challenges of Mental Math:

  • Few people can do mental math effectively.

  • Many struggle to fully understand mathematical concepts.

• Benefits of Using Computers:

  • Fast calculations.

  • Easy addition of visualizations to enhance understanding.

  • Facilitation of situation modeling, such as “what if interest rates go up by 0.25%?”

Caveats of Spreadsheet Use

• Speed Limitations:

  • Processing speed is not the highest possible.

  • More serious math may require applications like MATLAB.

• Errors in Sheets:

  • Errors are often hard to detect.

  • Detecting errors requires skill; do not blindly trust visually appealing graphs.

  • Errors can result in dramatic impacts.

Features of Spreadsheet Applications

• Organizing Data:

  • Layouts that facilitate the organization of tabular data.

  • Gridded layout available for both numeric and non-numeric data.

• Data Manipulation:

  • Options include basic operations like arithmetic and logical operations.

  • Availability of functions and programmable cells for advanced data manipulation.

• Data Visualization:

  • Capability to visualize both numbers and text using various charts and graphs.

Microsoft Excel

• Overview of Document Layout:

  • Quick access toolbar

  • Ribbon tab for various functions

  • Title bar indicating the filename (e.g., Book1 (version 1).xlsb [Autosaved])

  • Primary spaces:

  • Cell: A single location in an Excel sheet storing text, numbers, or formulas.

  • Row: A line of cells arranged horizontally, referenced by numbers.

  • Column: A vertical line of cells referenced by letters.

  • Worksheet: Workspace containing multiple rows and columns for data.

  • Workbook: An Excel document that contains at least one worksheet.

Creating an Excel Document

• Desktop Application Use:

  • Start the Excel app (or select File for an open document).

  • Double-click a blank workbook to start or select a template or previously saved document.

Working with Data

• Entering Data:

  • Options include typing or pasting data directly into cells.

  • Data can be generated via programmed cells.

• Selection Techniques:

  • Select a single cell by clicking.

  • Select a group of cells by click-and-drag methodology.

• Formatting:

  • Text formatting tools found under the Home tab; tooltips provide guidance for icons.

• Clearing Data:

  • Options to clear both content and formatting of cells.

Adding Rows and Columns

• Inserting New Rows and Columns:

  • Highlight rows/columns and use the Insert function.

  • New rows insert above selected rows; new columns insert to the left.

• Creating a Series of Numbers:

  • Click and drag from the bottom right corner of a starting cell (the fill handle) to replicate entries.

  • Numeric series can be generated by entering a few numbers for Excel to recognize the pattern.

• Alternative Tools:

  • Hold Ctrl while dragging for variations in series creation.

  • Use the menu for detailed filling options.

Naming Cells

• Unique Cell References:

  • Each cell has a reference defined by its column letter and row number (e.g., A2, BA24).

• Naming Multiple Cells:

  • Cells can be named by highlighting and using the Name Box or via the Define Name function in the Formulas tab.

Processing and Transforming Data

• Functionality of Data Processing:

  • Built-in mathematical or logical operations/functions are accessible.

  • Each function must start with an equal sign (=).

  • Functions do not include references back to their own cell to avoid circular references.

• Structure of Built-in Functions:

  • Formatted as func(var1, var2…).

• Examples of Formulas:

  • Arithmetic operation: $= A2*5+2$

  • Built-in function: $= average(A2:A9)$

  • Mixed arithmetic and functions: $= sum(A2:A9) * 5 + 2$

• List of Useful Functions:

  • Basic: sum, average, count, max, min

  • Conditional: sumif, averageif, countif, maxif, minif

  • Lookup function: vlookup.

• Relative vs. Absolute References:

  • Absolute references use $ (e.g., $A3$ stays the same regardless of copying).

  • Relative references (e.g., A3) update based on the location moved.

Example Calculations

• Example 1: Function Evaluation

  • Function: $y = 3x + 1$ for $x = 1$ to $20$ in steps of $2$

  • Steps:

  1. Insert column heading (e.g., x) and generate series for x.

  2. Insert another column for y using appropriate Excel operations (e.g., multiplication, division, power).

• Example 2: Function Evaluation

  • Function: $y = rac{1}{1+e^{-x}}$ for $x = -10$ to $10$ in steps of $0.1$

  • Steps:

  1. Generate series for x and y using Excel's division and exponential functions.

Visualizing Functions

• Graph Generation:

  • Use Insert Line Chart within the Insert tab to create a visualization of y versus x.

  • Line graphs plot dependent variables on vertical axes against independent variables on horizontal axes.

• Steps to Create Graph:

  1. Select location for the graph.

  2. Choose chart type (line chart).

  3. Add data using the Select Data option within the Chart Tools.

  4. For X-axis, establish ranges for x-values.

  5. Finalize selections to display the graph

Lookup Functions

• Using LOOKUP for Grade Calculation:

  • Structure: LOOKUP(lookup ext{}value, lookup ext{}range, [result ext{_}range])

  • Explanation of parameters:

  • lookupvalue: searches within lookuprange.

  • resultrange: provides data corresponding to lookupvalue location.

• BMI Calculation Example:

  • BMI formula: $ext{BMI}(kg/m^2) = rac{ ext{Weight (kg)} }{ ext{Height(m)}^2 }$

  • Classification table example for BMI ranges and their classifications.

• Practical Application in Data: Analyze BMI of 500 adults, categorizing them into classifications based on calculated BMIs.

File Formats

• Formats for Data:

  • CSV (Comma-Separated Values):

  • Text-based; each row separated by commas.

  • Easily generated and imported into spreadsheets.

Excel Summary

• Advantages:

  • Ubiquity in business applications.

  • Effective for organizing and entering data.

• Disadvantages:

  • Limited options for statistical computing.

  • Constraints in advanced data visualization capabilities.