Chapter 5: Complex Conditional and Retrieval Functions

DSUM Function

a database function that is ideal for setting up a criteria range and then calculating the sum based on the filters within that criteria range =DSUM(database, field, criteria)

Decision Tree

a diagramming tool that allows you to break down potential decisions in a logical, structured format

COUNTIFS Function

a function that allows for multiple criteria in multiple ranges to be evaluated and counted =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],...

AVERAGEIFS Function

a function that averages a range of data, selecting data to average based on the criteria specified =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)

AVERAGEIF Function

a function that averages the cells that meet the specified criteria =AVERAGE(range, criteria, [average_range])

INDIRECT Function

a function that can change a text string within a cell to a cell reference

Complex Function

a function that combines multiple functions within a formula

Conditional Aggregate Functions

a function that consolidates or summarizes a subset of data that has been filtered based upon one or more criteria

COUNTIF Function

a function that counts the # of cells that meet a specified criteria

HLOOKUP Function

a function that helps to retrieve values located in another location and is used when your comparison values are located in a row horizontally

NOT Function

a function that is used when there are many options that fit the desired criteria and only one option that does not fit the criteria

MATCH Function

a function that looks for a value within a range and returns the position of that value within the range

IF Function

a function that returns 1 of 2 values depending on whether the supplied logical test being evaluated is true or false

AND Function

a function that returns TRUE if all logical tests supplied are true; otherwise, it returns false

Logical Function

a function that returns a result, or output, based on evaluating whether a logical test is true or false

IF Function

a function that returns one of two values depending on whether the supplied logical test being evaluated is true or false

Nested IF Function

a function that uses IF Functions as arguments within another IF Function and increases the # of logical outcomes that can be expressed

Conditional Math Function

a function that will calculate only when specified condition is met

IFERROR Function

a function used for detecting an error and displaying something more user-friendly than the error message =IFERROR(value, value_if_error)

Competitive Advantage

a strategic advantage that a business has over its competition

Excel Database

a way of storing data in Excel that is made up of records (rows) and fields (columns)

Logical Test

an equation with comparison operators that can be evaluated as either true or false -aka a logical expression

Static Data

data that has been manually calculated and then typed into a worksheet

VLOOKUP Function

matches a provided value in a table of data and returns a value from a subsequent column, helps to retrieve values located in another location and is used when your comparison values are located in a column vertically to the left of the data you want to find -paired with Exact Match

A1 Reference Method

refers to the way cell references are written

R1C1 Reference Style

refers to the way cell references are written, if #s appear for the column headings, the reference style for Excel is currently R1C1

OR Function

returns TRUE if any one logical test supplied is true; otherwise, returns FALSE

Pseudo Code

rough draft of a formula/code intended to help you understand and determine the structure of a problem before you develop the formula

Double Prime Symbol

straight quotation marks that are used in functions and formulas to let Excel know that the element is a text string and not a numeric value, cell reference, or ranged range ex) "No Bonus"

SUMIF Function

sums a # of cells that meet a specified criteria =SUMIF(range, criteria, [sum_range])

SUMIFS Function

sums a range of data, selecting data to total based on the criteria specified =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2],...)

Logical Operator

used to create logical tests and includes <, >, <=, >=, and <>

INDEX Function

works in conjunction with the MATCH Function; returns the value of an element in a table/array selected by the row and column # and has two argument lists to select =INDEX(array, row_number, [column_number]) row # is the row in which value will be found