Chapter 2: Formulas and Functions: performing Quantitative Analysis

COUNT functions

3 basic count functions: COUNT: tallies the number of cells in a range that contains values you can use in calculations (such as numbers Al and date data) but excludes blank cells and text entries. -cell D7 in attached image. COUNTBLANK: a statistical function that tallies the number of cells in a range that are blank. -cell D8 in attached image. COUNTA: a statistical function that tallies the number of cells in a range that are not blank (cells that contain data whether it's text/value/formula). -cell D9 in attached image.

TODAY function

A date & time function that displays the current date. -does not require arguments but must include parentheses. -automatically updates function when you open or print the workbook. =TODAY()

NOW function

A date and time function that calculates the current date and military time that you last opened the workbook using the computer's clock. -date and time will change everything the workbook is opened. =NOW()

Formula Autocomplete

A feature that displays a list of functions and defined names that match letters are you type a formula.

PMT function

A financial function that calculates the periodic loan payment given a fixed rate, number of periods (aka term), and the present value of the loan (the principal). =PMT(rate,nper,pv,[fv],[type]) Rate: the interest rate per pay period (PMT calculates periodic payment, so the units of each argument must be converted to that of the same periodic unit). -ex: banks rates are usually given annual rates, so the annual rate must be converted to the monthly rate (so annual rate should be divided by 12). -if the rate is quarterly, divide by 4. NPER: the total number of payment periods (to calculate, multiply the number of years by the number of payments in 1 year). PV: the present value of the loan (results of the PMT function is a negative value because it represents debt, you can display the results in a positive value by typing a minus sign in front of the PV cell reference in the PMT function).

IF function

A logical function that evaluates a condition and returns one value if the condition is true and a different value if the condition is false. 3 arguments: Logical Test: any value or expression that can be evaluated to be true or false. Value if True: the value returned if the logical test is TRUE (if omitted, TRUE is returned). Value if False: the value returned if the logical test is FALSE (if omitted, FALSE is returned). =IF(logical_test,[value_if_true],[value_if_false])

VLOOKUP function

A lookup and reference function that accepts a value, looks the value up in a vertical lookup table with data organized in columns, and returns a result. -contains 3 arguments and 1 optional argument. 3 arguments: Lookup Value: the cell reference of the cell that contains value to lookup. Table Array: the range that contains the lookup table. Column Index Number: the column number in the lookup table that contains the return values. Optional argument: Range-lookup: an argument that determines how the VLOOKUP and HLOOKUP functions handle lookup values that are not an exact match for the data in the lookup table. *** enter FALSE here to look up an exact match. *** enter TRUE to enable VLOOKUP to find the nearest value. How VLOOKUP Works: 1. The first argument evaluates the value to be located in the left column of the table array. 2. Excel searches the first column of the table array until it (a) finds an exact match or (b) identifies the correct range. 3. If excel finds an exact match, it moves across the table to the column designated by the column index number on the same row, and returns the value in the stored cell. If the last argument is TRUE or omitted then excel is looking for an approximate value. =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]

HLOOKUP function

A lookup and reference function that accepts values, looks the value up in a horizontal lookup table with data organized in rows, and returns the results. -data must be arranged in ascending order from left to right. Syntax: similar to VLOOKUP except the 3rd argument is row_index_num instead of col_index_num. =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]

Arguments

A positional reference contained within parentheses in a function such as a cell reference or value, required to complete a function and produce output. -a functions ________ are enclosed in parentheses and specify the inputs that are required to complete the operation.

Function

A predefined computation that simplifies creating a complex calculation and produces a result based in inputs known as arguments. -must adhere to syntax when using functions. Primary Function Categories: -Date & Time -Financial -Logical -Lookup and Reference -Math & Trig -Statistical Name: the function name describes the purpose of the function (ex. SUM indications that the function adds/sums values).

Lookup Table

A range that contains data for the basis of the lookup and data to be retrieved (table array is a range containing a table of values and text from which data can be retrieved). -table should contain at least 2 rows and 2 columns, not including headings. If the range lookup = FALSE: -the 1st column can be in any order. If the range lookup = TRUE: -must arrange data from the lowest to highest values and include only the lowest value in the range (breakpoint).

Syntax

A set of rules that governs the structure and components for properly entering a function. -start a function with an equal sign, followed by the function name, and then it's arguments enclosed in parentheses.

AVERAGE function

A statistical function that calculates the arithmetic mean, or average, of values in range. Attached example in Cell D3 of attached image.

SUM function

A statistical function that calculates the total values contained in two or more cells. -contains 1 required argument that represents a range of cells to add. -number2 is an optional number that is used if you want to sum values in nonadjacent cells or ranges.

Insert Function Dialog Box

Can also be used to search for a function, select a function category, and select a function from the list. To display this box: -click insert function (located between the Name Box and the Formula Box). Or -click Insert Function in the Functional Library Group on the Formulas Tab. Click "Help on this Function" to display details about the selected function.

absolute cell reference

Cell reference that does not adjust to the new cell location when copied or moved, remains constant to a specific cell location. -designated with a $ sign before the column and row numbers (ex. $B$2).

Error in formula

If an unexpected error occurs when using the Fill Handle to copy a formula, start troubleshooting by checking the formula to see if an absolute cell reference is needed. Attached image shows what happens if B4 is referenced relatively instead of an absolute cell reference.

MIN and MAX functions

MIN: a predefined formula that displays the lowest value in a range. -in cell D5 of attached image. MAX: a statistical function that identifies the highest value in a range. -in cell D6 of attached image.

Function Arguments dialog box

Once you have clicked OK on the function you want, it will open this box so that you can enter the arguments for that specific function. -arguments in bold are required. -arguments not in bold are optional.

quantitative analysis

Taking physical measurements and making mathematical computations to reach a conclusion

Logical Test

The 1st argument for the IF command. Requires a comparison between at least 2 variables. Comparison operators are attached

Value if true/false

The 2nd and 3rd arguments of the IF command.

Inserting a function

To use a function, type the equal sign and then the name of the function or you can locate the function from the Formulas Tab or by using the Insert Function Dialog Box.

Nesting functions

Using one function within another function. Attached is an example of the combination of the MAX function nesting in the VLOOKUP function

Mixed cell reference

a cell reference that contains both relative and absolute references, where part of the cell reference will change and part will remain constant. -examples: A$2 or $B3

Median Function

a predefined formula that identifies the midpoint value in a set of values. See attached image, cell D4, for example.

Quick Analysis

a set of analytical tools you can use to apply formatting, create charts or tables, and insert basic functions. -when you select a range of data, the Quick Analysis button displays adjacent to the bottom right corner of the selected range. In attached image

Relative cell reference

cell reference that adjusts to a new location when copied or moved. -ex. Formula containing the cell A1 is copied down one row in the column, the reference would become A2. -default method.

F4 key

excel cycles through the different reference types starting by changing a relative reference to an absolute reference.

Function ScreenTip

small pop-up description that displays the function arguments. -the argument you are currently entering is bold in the _____ ______.

Breakpoint

the lowest value for a category or in a series