K201 Ch 11-17 Exam

What does the SUM function do?

Adds a range of numbers

What does the AVERAGE function do?

Calculates the mean of a range

What is a cell reference?

The location of a cell (ex: A1)

Difference between relative and absolute references?

Relative changes when copied (A1), Absolute stays fixed ($A$1)

What does the $ symbol do in a formula?

Locks a cell reference so it doesn’t change when copied

What is the difference between COUNT and COUNTA?

COUNT = counts numbers only, COUNTA = counts all non-empty cells

What is mixed cell referencing?

Locks only one part of a reference ($A1 locks column, A$1 locks row)

When should you use absolute references?

When a value (like a rate or constant) should stay the same in all formulas

What does =TODAY( ) do?

Returns the current date and updates automatically

What does CTRL + ; do?

Inserts the current date that does NOT change

What is the syntax of the DATEDIF function?

=DATEDIF(start_date, end_date, “unit”)

What units can DATED IF use?

“Y” = years, “M” = months, “D” = days

How do you calculate whole weeks between two dates?

Use: =INT(DATEDIF(A1, B1, “D”)/7)

What does the INT function do?

Rounds a number DOWN to the nearest whole number

What does the ROUND function do?

Rounds a number to a specified number of decimal places

Example of 3D referencing

=SUM(‘Sheet1:Shee4” !E6)

What does COUNTIFS do?

Counts cells that meet one or more conditions

What does SUMIFS do?

Adds values that meet specified conditions

When do you need quotation marks in formulas?

For text or logical conditions (ex: “IL”, “>=10”)

What is a named range?

A custom name for a cell or range used in formulas

How do you create named ranges from a table?

Select data → Formulas → Create from Selection → Top row

How do you calculate order quantity?

=MaxUnits - InStock

How do you prevent negative order quantites?

=MAX(H6-G6, 0)

What is a conditional aggregate function?

A function that calulates (count, sum, avg, etc.) based on conditions

What does TEXTBEFORE do?

Returns text before a specified delimiter

What does TEXTAFTER do?

Returns text after a specified delimiter

What does TEXTSPLIT do?

Splits text into multiple cells based on a delimiter

What do LEFT, RIGHT, and MID do?

Extract specific characters from text

What does TRIM do?

Removes extra spaces (except single spaces between words)

What does CLEAN do?

Removes non-printable characters

What does PROPER do?

Capitalizes the first letter of each word

How do you combine text in Excel?

Using & or CONCAT

What is Flash Fill?

A tool that automatically fills data based on patterns (Ctrl + E)

What does VLOOKUP do?

Searches down the left column and returns a value from the same row to the right

What does the range_lookup argument control in VLOOKUP?

Whether the match is exact (FALSE) or approximate (TRUE)

What is a major limitation of VLOOKUP?

It can only look to the right of the lookup column

What does HLOOKUP do?

Searches across the top row and returns a value from below

What does the MATCH function return?

The position (index) of a value in a row or column

What match type is most commonly used in MATCH?

0 (exact match)

What does XLOOKUP do?

Finds a value in a range and returns a corresponding value from another range

Why is XLOOKUP better than VLOOKUP?

It can look in any direction and doesn’t require column numbers

What does the INDEX function do?

Returns a value based on a specified row and column position

Why combine INDEX and MATCH?

To create a flexible lookup that works in any direction

What does the IF function do?

Returns one value if a condition is TRUE, another if FALSE

What is the syntax of IF?

IF(logical_test, value if true, value_if_false)

What is a logical_test?

A condition that evaluates to TRUE or FALSE using operators like =, >, <, >=, <=, <>

When do you use quotes in IF?

Use quotes for text ("Yes"), not for numbers (100)

When do you use AND?

When ALL conditions must be TRUE

When does AND return TRUE?

Only when all conditions are TRUE.

When do you use OR?

When at least ONE condition must be TRUE

When does OR return TRUE?

When any condition is TRUE

When do you use nested IF?

When there are more than 2 possible outcomes

How many IFs do you need?

Number of outputs − 1; Excel stops at first TRUE condition.