Formulas Flashcards

Which function returns the position of a value within a range?

Answer: MATCH

MATCH returns the relative position of a value within a one-dimensional range.

What does the formula =IFERROR(A1/B1, "Error") return when B1 is 0?

Answer: Error

IFERROR returns the second argument whenever the first argument produces any error, including #DIV/0!.

Which of the following is a valid absolute cell reference in a formula?

Answer: $A$1

$A$1 locks both the column and row so the reference doesn't change when copied.

What result does =MOD(17, 5) return?

Answer: 2

MOD returns the remainder after division; 17 ÷ 5 = 3 remainder 2.

Which function counts only the cells in a range that contain numbers?

Answer: COUNT

COUNT tallies only numeric values, ignoring text, blanks, and errors.

The formula =LEFT("PracticeTest", 8) returns which string?

Answer: Practice

LEFT extracts 8 characters from the start of the text string, yielding "Practice".

What happens when you enter a formula that creates a circular reference in Excel?

Answer: Excel displays a warning and may return 0 or an error

By default Excel warns the user about circular references and typically shows 0 unless iterative calculation is enabled.

Which function combines the results of multiple IF conditions without nesting?

Answer: IFS

IFS evaluates multiple condition-value pairs in order and returns the value for the first TRUE condition.

What does =CONCATENATE("Hello", " ", "World") return?

Answer: Hello World

CONCATENATE joins text strings in order; the space in the middle argument produces "Hello World".

The ROUND function =ROUND(3.567, 2) returns which value?

Answer: 3.57

ROUND with 2 decimal places rounds 3.567 to 3.57 because the third decimal (7) is ≥ 5.

Which formula correctly calculates a weighted average for values in A1:A3 with weights in B1:B3?

Answer: =SUMPRODUCT(A1:A3,B1:B3)/SUM(B1:B3)

SUMPRODUCT multiplies each value by its weight and sums the results, then dividing by total weight gives the weighted average.

What does the & operator do in an Excel formula?

Answer: Concatenates text strings

The & operator joins (concatenates) text values, functioning identically to the CONCATENATE function.

Which function returns the current date and time, updating each time the workbook recalculates?

Answer: NOW()

NOW() returns the current date and time as a serial number that recalculates whenever the worksheet changes.

In the formula =VLOOKUP(D2, A2:C10, 3, FALSE), what does the argument FALSE specify?

Answer: Require an exact match

FALSE (or 0) as the fourth VLOOKUP argument forces an exact match, returning #N/A if none is found.