Understanding Spreadsheets: Concepts, Formulas, and Functions

Posted on Mar 10, 2025 in Computers

Understanding Spreadsheets

Spreadsheets originated with the idea of creating programs to perform simple calculations (1974). The first program to load a worksheet was VisiCalc, which allowed for grid calculations.

Initial Concepts

  • Worksheet: A grid that allows calculations with rows and columns.
  • Row: A horizontal series of cells.
  • Cell: An individual box in the worksheet used to enter numeric, alphabetic, or formula-based data.
  • Reference: The name of a cell, used to refer to its contents in formulas.
  • Workbook: A file that contains one or more worksheets.

Microsoft Excel 2003

  • Rows: 65,536
  • Columns: 256
  • Total Cells: 65,536 x 256 = 16,777,216 per worksheet

Format Cells

  • Number: Specifies the number of decimal points and thousands separators.
  • General: Displays numbers as entered, but very large or small numbers are shown in scientific notation.
  • Currency: Adds a currency symbol, decimal points, and displays negative numbers in red.
  • Percentage: Multiplies the number by 100 and adds a percentage sign (%).

Formulas

  • Cell Reference: Used to incorporate the content of a cell into a formula. Example: =(C3/C4)
  • When a reference is copied, it adapts to the new location unless specified otherwise.
  • Relative Reference: Adjusts automatically when copied to a new row or column.
  • Absolute Reference: Remains constant when copied. Denoted by a $ symbol before the column and row. Example: $A$23
  • Mixed Reference: Either the row or column is fixed.
    • Fixed Row: A$23
    • Fixed Column: $A23

You can use references from the same sheet or from another sheet: [Sheet2]. To reference from another workbook: [Book.xls]Sheet2!reference.

  • Constants: Numbers.
  • Arithmetic Operations: ^ (power), * (multiplication), / (division), + (addition), - (subtraction), % (percent)
  • () Parentheses set the order of operations.
  • All formulas start with =.

Functions

A function is a pre-built formula that performs a specific operation. Syntax: =FUNCTION_NAME(argument)

  • Name: The reserved name of the function.
  • Argument: The data (cell references, data, other functions) that the function acts upon.
  • Multiple arguments are separated by ;.
  • Optional arguments can be omitted.
  • Round: =ROUND(data, number_of_decimals)

Function Types

  • Financial
  • Date and Time
  • Math and Trigonometry
  • Statistics
  • Search and Reference
  • Database
  • Text
  • Logical
  • Information

Some Functions

  • =MIN(range): Returns the minimum value in a series of numerical data.
  • =MAX(range): Returns the maximum value in a series of numerical data.
  • =AVERAGE(range): Calculates the arithmetic mean of a set of values.
  • =COUNT(range): Determines the number of values in a range of cells.

Logical Functions

  • =IF(condition, value_if_true, value_if_false)
  • Example: =IF(score >= 5, "Approved", "Pending")