BUS 1350 - Exam 2 Chapter 5

=IF

(logical_test, [value_if_true],[value_if_false]) use when you have only ONE test/condition

Critieria for an INDIRECT function

- cannot be used alone - must have names ranges - tables must be set up the EXACT same way

=XLOOKUP

- meant to replace both the VLOOKUP and HLOOKUP Functions - it will also replace the INDEX/MATCH function combo that was previously used to perform more powerful VLOOKUPs

=IFS

- similar to a nested IF function, the IFS function checks if one or more conditions are met and returns a result on the first true condition met. - you can test up to 127 different conditions with the IFS function

=AND

- use when you have only ONE test/condition; by itself - it can ONLY return a value of either True or False; will return TRUE if ALL logical tests supplied are true; otherwise, it returns FALSE

=OR

- use when you have only ONE test/condition; just as with the AND function, by itself it can ONLY return a value of either True or False - will return TRUE if ANY ONE logical test supplied is true; otherwise, it returns FALSE

=INDIRECT

- used to indirectly reference cells, ranges, other sheets, or workbooks - lets you create an Excel dynamic cell reference or range reference rather than "hard coding" those references into your formulas - as a result, you can change a cell reference within a formula without changing the formula itself

Purpose of the indirect function

Being able to change a cell reference without having to rewrite a formula is the purpose of indirect referencing in which the reference itself is a calculated value

4 most common logical functions

IF, AND, OR, NOT

Logical function

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

Logical test

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

Decision tree

diagramming tool that allows you to break down potential decisions in a logical, structured format - you can take a problem or decision and break down the potential possibilities

There are not any _________________________________ for an IFS statement. The last logical test in every IFS function will be TRUE

false arguments

=MATCH

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

The MATCH function returns a _______________________

number

The number of IF functions needed is always ___________________ than the number of outcomes

one less

=INDEX

returns a value or the reference to a value from within a table or range

=NOT

returns the reverse of the logical value to which the logical test evaluates; is used predominately when there are many options that fit the desired criteria and only ONE option that does not

The number of parentheses needed to close your functions at the end of your formula is always the ______________ number as the number of _________ functions you used

same, IF

=IFS or nested IF functions

use when you have MORE THAN ONE test/condition

The INDEX function returns a __________________

value