Excel Functions and Formulas - Microsoft Office 2013

Microsoft Office 2013 - Formulas and Functions

• This chapter covers quantitative analysis using formulas and functions in Excel.
• Excel offers three types of cell references:
  • Absolute: Fixed addresses (AAA1) don't change when copied.
  • Relative: Addresses adjust relative to the new location (A1).
  • Mixed: Part of the address is fixed, the other adjusts (A1orAA1 or AA1orA1).
• The F4 key toggles through these references.

Absolute Cell References

• An absolute cell reference provides a permanent reference to a specific cell.
• The address will not change when the formula is copied.
• Example: If a formula references BBB4, this reference will remain BBB4 even when copied to another cell.

Mixed Cell References

• Mixed references have one part (row or column) fixed and another relative.
• $B4: Column B is fixed
• B$4: Row 4 is fixed

Avoiding Circular References

• A circular reference occurs when a formula refers to itself.
• Excel displays an error message and shows a green triangle in the top-left corner.

Function Basics

• An Excel function is a predefined formula for calculations.
• Categories of functions include Date & Time, Financial, Logical, Lookup & Reference, Math & Trig, and Statistical.

Function Terminology

• Syntax: Rules for forming functions.
• Arguments: Inputs (cells, ranges) for functions.
• Functions typically start with an equal sign (=), followed by the function name and arguments in parentheses, e.g., =SUM(A1:A3).

SUM Function

• The SUM function calculates the sum of numbers in cells or ranges.

AVERAGE Function

• The AVERAGE function calculates the arithmetic mean of a range of cells.

COUNT Function

• The COUNT function counts the number of numeric values in a range.

COUNTBLANK Function

• The COUNTBLANK function counts the empty cells in a range.

COUNTA Function

• The COUNTA function counts the number of non-empty cells in a range.

MIN Function

• The MIN function returns the smallest value in a range.

MAX Function

• The MAX function returns the largest value in a range.

MEDIAN Function

• The MEDIAN function returns the midpoint value in a range.

VBA Functions (INT,ROUND,MOD)

• Various functions exist within Excel to help users with calculations and data analysis
• INT rounds down to the nearest whole number
• ROUND: rounds to a specified number of decimals
• MOD: finds the remainder after division (number, divisor)

Date Functions

• Excel treats dates as numbers.
• Calculations (e.g., subtraction) are possible.
• TODAY(): Displays the current date.
• NOW(): Displays the current date and time.

IF Function

• The IF function determines results based on a logical condition:
• =IF(logical_test, value_if_true, value_if_false)

Designing the Logical Test

• Logical tests use logical operators. Examples include:
  • =, <>, <, >, <=, >=
• Text comparisons require quotes.

VLOOKUP Function

• VLOOKUP searches a lookup table (vertical table) for a value and returns the corresponding value from another column.
• Requires the lookup value, the table array, and the column index of the return value.

HLOOKUP Function

• Similar to VLOOKUP, but searches a horizontal table.
• The third argument specifies the row index.

PMT Function (Calculating Payments)

• The function determines periodic payments on a fixed rate (interest) loan.
• The function has three required arguments:
  • Rate, Nper, and PV.

Range Names

• Range names (e.g., Rate, Hours) are assigned to cell(s) for easier formula reading.
• Rules govern range name creation. Length is limited, and needs to start with a letter or underscore.

This quiz explores the use of formulas and functions in Excel, focusing on different types of cell references including absolute, relative, and mixed. Additionally, it addresses the importance of avoiding circular references in spreadsheet calculations. Test your knowledge on how these concepts enhance quantitative analysis in Excel.