Please enable JavaScript.

Coggle requires JavaScript to display documents.

Ch.7 Spreadsheets - Coggle Diagram

- - - - Max
      - Average
      - count
      - Countie
      - large
      - Large
      - small
      - mode
  - - - e.g. They can be used to sum up a series of numbers and
        round up numbers.
    - - sum
      - sumLF
      - round
      - INT
      - MOD
      - POWER
      - RAND
  - - - IF(<condition>,X,Y>)
      - and(<condition1>,<condition>2>,...)
      - OR(<condition1>,<condition>2),...
      - OR (<condition1>,<condition>2>,...)
      - NOT(<condition>)
  - - - code(<text>)
      - char(<number>)
      - Len(<text>,N)
      - Left(<text>),N)
- - - - Display the trend over time.
    - - Compare the proportions or percentages
        contributed by several categories.
    - - Compare data in several categories.
    - - Illustrate the correlations between two data
        series.
    - - Show the trend of stock price.
  - - - Mark each data series with a title whenever possible
      - Do not include any blank cells between data.
      - Arrange each set of chart data either in rows or columns
- - - - number
      - text
      - date
- - - - Select a cell
      - Enter data into the cell
      - Press 'Enter' to complete the input
    - - Press the function key 'F2' and then change the cell content in the cell
      - Double-click the the cell and then change the cell content in the cell
      - Click the formula bar and then change the cell content in the formula bar
    - - Cell Format/Display
        
        General
        
        Currency
        
        Data
        
        Time
        
        Fraction
        
        1 more item...
  - - - Copy and paste
      - Extending the series
    - - Microsoft Excel tries to determine the pattern of data in the source.
      - If Microsoft Excel fails to identify the pattern, it simply fills up the adjacent cells
        repeatedly with the value in the source.
      - Examples:
        
        the source which is a single cell
        
        textual data without any number or not related to time and date
    - - If Microsoft Excel successfully determines the pattern of data in the source, the
        adjacent cells are filled with values by extending the series.
      - AutoFill can be used to create number sequences, time lists, day lists, week lists,
        month lists and regular text patterns.
- - - - Relative cell reference
        
        It is formed by a column letter and a row number, e.g. A1 and B5
        
        When a cell containing a relative cell reference is copied and pasted into another, cell, Microsoft Excel calculates the relative position of this cell and the location of the target cell. The cell reference in the target cell is changed accordingly.
      - Absolute cell reference
        
        When a formula is copied or moved to other cells, the absolute cell reference in the formula remains unchanged.
        
        It identifies a specific cell by adding a symbol ‘$’ in front of the column letter and
        the row number, e.g. $A$3 and $F$10.
      - Mixed cell reference
        
        When a formula is copied to other cells, the column letter or the row number with a ‘$’ remains unchanged, while the other part of the reference without the ‘$’ changes in the same way as a relative cell reference.
        
        It has a ‘$’ either before the column letter or the row number, e.g. $A5 and A$1.
- - - - what-if scenario analysis
      - pivot table
  - - - Scenario Manager
        
        allow users to create several sets of input values for specific cells, called scenario
        
        User can switch between scenarios and compare the results in different scenarios easily.
        
        Can generate a summary report on the scenarios automatically
        
        Steps
        
        Design the model to be used in the scenario
        
        2 Identify the scenario values and the target cells that change with the scenario cells.
        
        Create scenarios with the Scenario Manager.
        
        Switch to one scenario in the Scenario Manager and view the results.
        
        Generate a summary report with the summary function in Scenario Manager if necessary.
      - Goal Seek
        
        An analysis tool to find out how the changes in variables affect the outcome.
        
        Find out the input value of the formula if the desired end result of a formula is known..
        
        Can only work with one changing cell in the ‘Number’ cell format.
- - - - input
      - organized
      - create charts
      - perform data analysis
    - - Microsoft Excel
      - OpenOffice
      - Corel Quattro Pro