COSC1371                                         TEST-2 Review                      November 2011

                                                                    

A.     EXCEL

1.        Shortcut Keys:

TAB:              Next cell of the same row, or first cell of next row or creates a new row after last row.

CTRL-HOME: Cell A1

CTRL-END:     Last cell

CTRL->:             Last column of the same row (in steps of non-empty cells)

CTRL <-:          First column of the same row. (in steps of non-empty cells)

F2:                       Enter the Edit Mode (just like double-clicking a cell)

CTRL-D:          Fill the selected cell(s) with the value from the cell above it.

CTRL-R:          Fill the selected cell(s) with the value  from the cell to its left.

CTRL-`:           Show/Hide formulas (the symbol ` is the one to the left of 1 at top of the keyboard)

 

2.    WorkSheet Size:            Last Cell: XFD (16384), 1048576

3.        Data Types:  Number, Label, Formula (“=” must appear in the first position of a cell)

4.    Cell references:    Absolute($E$9): Adjusted only when the cell identification itself changes

                                                                       due to inserion/deletions of other cells/rows/columns

                                     Relative (E9)     Automatically adjusted when copied as well as above cases

5.        Automatic Range conversion:

A3:F10 (Standard)        A10:F3                   F3:A10                   F10:A3

6.        Function categories on Insert Tab.

                All, Financial,  Statistical,  Math & Trig, Lookup & Reference, Data & Time and Logical,

7.        Financial functions:

                FV(0.5%, 12, -1000)   = $12,335 > 1000*12                       

FV(0.5%, 240, -1000) = $462,040 > 1000*240

                PMT (rate, nPer, Principal)

                                PMT(5%, 120, -100000)= $1060.66  > 100000/120

                                PMT(5%, 120, -200000) = $2121.32 > 200000/120

                                PMT(5%, 240, -100000) = $659.96   > 100000/240

 

                SLN (Cost, Salvage, Life): Straight-line depreciation (same depreciation amount for all periods)

                    SLN (20000, 5000, 5) = 3000  (The same for each of 5 years of useful life)

                SYD(Cost, Salvage, Life, Period)  Sum-of-Years Digits Dep (Amount decreases as time goes on)

                    SYD (20000, 5000, 5, 1) = 5000

                    SYD (20000, 5000, 5, 2) = 4000

                    SYD (20000, 5000, 5, 3) = 3000

                    SYD (20000, 5000, 5, 4) = 2000

                  SYD (20000, 5000, 5, 5) = 1000

                DDB(Cost, Salvage, Life, Period) Double-Declining Dep ( Amount decreases as time goes on)

                For the First period (year)  DDB > SYD > SLN

                For the last period (year)    DDB <  SYD < SLN

8.        Some Statistical and Other functions.

               Statistical Functions:

                COUNT (H10:H14): counts cells that have numbers including Date/time

                COUNTA (H10:H14): counts cells in the range that are not empty

                COUNTIF (B15:H15, “>8”): counts number of cells in the given range that meet given

condition.

SUM(A5:A9)       IF(Condition, TrueValue, FalseValue)

                IF (F2>F3, F2, F3) = to take the larger of F2 or F3

                POWER(4,3) = 4*4*4 =  64

9.        AutoFill Option using the Fill Handle

       Convenient way to copy a block of cells to any number of adjacent blocks of cells

       Auto Fill Options Mini-Menu lets us choose (1) Copy Cells, (2) Fill Series or (3) Fill Without Formatting.

10.   Charts

Compare values across different categories, effective for small number of categories (<=7),

X-axis (category Axis), Y-axis (value axis), vertical bars for values. Row Headings and Column Headings selected automatically become Categories and Data Series, respectively, of the chart being inserted.

Horizontal bars for values, effective for long category names,  several subtypes (more like

Column charts) including Clustered, Stacked, 100% Stacked, 3-D, Cylinder, Cone, Pyramid. 

Lines connecting data points to show trends over equal time intervals, Category axis (X-axis)

represents time with or without data point markers 

Each data point represents a proportion of the whole data series, good only one single data series, slices can be exploded.

a.        Area charts: More like line charts (trends over time)

b.       XY (Scatter) charts: Helps to determine existing relationships between two sets of values.

c.        Doughnut charts: More like Pie charts except that they have multiple rings each representing a data series so they are good for multiple data series.

 

B.     ACCESS

 

1.        Need to name a file before starting. (Not any more)

2.        Objects: A database file contains various objects:

A table has records and all records of a table a common set of fields. A row of a table represents a record and its columns represent fields and a field shows a particular value of each and every record of a table.

3.        Various Views:  

4.        Table building:             Rows=Records                     Columns=Fields

Design View: For defining field names, their data types and properties(optional)

    Datatypes: Text (default), number, Date/Time

DataSheet View: For entering record information

5.        Some useful shortcuts:  

CTRL’    Insert the value from the same field just above

              CTRL=     move to first blank record 

TAB       Next field of same record or first field of next record

F11          Navigation Pane Shutter Bar Open/Close

CTRL+   Adding a record

CTRL-    Deleting a record

6.        Navigation Pane: Expands automatically when Access is open. This is where you organize various objects existing in the database file that is open. When the menu is open it shows two Option Lists:

·         Navigate to Category.

Custom

Object type

Tables and Related Views

Created Date

Modified Date

·         Filter by Group

Options vary according to the option selected in the above List.

For example when the “Tables and Related Views” option is chosen, you see the following options:

Tables

Queries

Forms

Reports

All Access Objects.

7.        Using Compact and Repair Database

Database needs to be compacted regularly for efficiency sake. This command is available in the “info” Option of the File Tab which is the default option of the File Tab.

8.        Setting up Filters  (Selecting certain records meeting certain selection criteria) by “Advanced Filter/Sorting”

                Command.

9.    Primary key field (Optional)  No two records of a table should have same key field value

                                                      No two tables of the DB should have same key field name.

                                                       Often for establishing relationships between two tables.

10.    Object Importing/Exporting:  Both cases, a DB file must preexist.

11.   Results of filter application saved as a “QUERY” by “SAVE AS QUERY” button

12.   After a query has been created from a single table, any legitimate change made to the query will also

       automatically change the underlying table and vice versa. 

13.  One-to-many relationship is created from the primary key of a Primary table to a foreign key of the

       Related table and not the other way around. “Cascade Update Related Fields” option is to direct Access

       to automatically update all foreign key values in a related table when the Primary key value is modified   

       in the Primary table.  

14.  When the Referential Integrity is enforced,  (a) you cannot enter a foreign key value in the related table

       unless the Primary key value exists in the Primary table and (b) you cannot delete a record in the    

       Primary table if it has a related record in other related tables.

15.  Filter Criteria definition Example:

----------------------------------------------------------------------

Field       Day                         Time

Sort         Ascending            Descending

          Criteria         TUESDAY             <10:00 or >16:00

                OR          MONDAY

       -----------------------------------------------------------------------

Note that in the above example, (1) Tuesday classes either before 10AM or after 4PM are to be selected while (2) all Monday classes are to be selected regardless of Time.