Info Systems Exam #2

Here's a comprehensive study guide based on the provided topics:

Microsoft Excel Concepts Study Guide

I. Core Excel Interface Elements

* Name Box: This displays the cell reference or the name of a selected cell or range.

* Formula Bar: This is where you enter or edit formulas or data in a cell. It displays the contents of the active cell.

* Active Cell: The cell currently selected in the worksheet. It has a bold border around it (often green).

II. Data Entry and Formatting

* Equal Sign (=): Essential to begin any formula or function in Excel. Without it, Excel treats the entry as text.

* Number Formats:

* Number: General display of numbers.

* Accounting: Aligns decimal points and often uses currency symbols.

* Currency: Similar to accounting but offers more control over the placement of the currency symbol.

* Percent: Displays values as percentages.

* Formatting vs. Value: Changing a number format only changes how the value is displayed, not the underlying value itself.

III. Formulas and Functions

* Formula: A user-defined calculation, like =A1+B1.

* Function: A pre-defined formula provided by Excel, like SUM(A1:A10).

* Order of Operations (PEMDAS): The order in which calculations are performed: Parentheses, Exponents, Multiplication and Division (from left to right), Addition and Subtraction (from left to right).

* Inserting Functions: Excel provides various methods to insert functions, such as using the Insert Function dialog box.

* Function Syntax: Every function has a specific structure. Incorrect syntax will cause errors. Know how to write functions correctly.

* Arguments in Functions:

* Arguments are values or cell references within the parentheses of a function.

* Commas (,) separate arguments.

* Optional arguments are shown in square brackets [ ] or are unbolded in the Insert Function dialog box.

* Cell Range: A selection of two or more cells. Written as A4:C20, it includes all cells from A4 through C20 and all cells in between.

IV. Cell Referencing

* Relative References: Cell references that change when a formula is copied to another cell. Example: A1.

* Absolute References: Cell references that remain constant when a formula is copied. Dollar signs ($) are used to "lock" the row and/or column. Example: $A$1.

* Mixed References: A combination of relative and absolute referencing. Either the row or the column is fixed. Example: $A1 (column locked), A$1 (row locked).

* F4 Key: Use this key to cycle through relative, absolute, and mixed cell references.

* Impact of Dollar Signs: The dollar sign ($) locks the column or row reference, preventing it from changing when the formula is copied.

* Viewing Formulas: Use CTRL + ` or the "Show Formulas" button to display formulas in cells instead of calculated values.

V. Functions

* VLOOKUP: Know this function very well. Understand its arguments (lookup value, table array, col_index_num, range_lookup) and what it returns.

* IF: Know this function very well. Understand how to use logical tests, value_if_true, and value_if_false.

* MIN: Returns the smallest number in a set of values.

* MAX: Returns the largest number in a set of values.

* SUM: Adds all the numbers in a range of cells.

* AVERAGE: Calculates the average of a set of numbers.

* LARGE: Returns the nth largest value in a data set.

* SMALL: Returns the nth smallest value in a data set.

* TODAY: Returns the current date.

* NOW: Returns the current date and time.

* PMT: Calculates the periodic payment for a loan or annuity.

VI. Logical Operators

* Used in functions like IF to create logical expressions.

* = Equal to

* > Greater than

* >= Greater than or equal to

* < Less than

* <= Less than or equal to

* <> Not equal to