Formulas Flashcards

What does =INDEX(A1:C5, 3, 2) return?

Answer: The value in row 3, column 2

INDEX returns the value at the intersection of the specified row and column within the range; row 3, column 2 here.

Which operator is used to raise a number to a power in an Excel formula?

Answer: ^

The caret (^) is Excel's exponentiation operator; =2^10 returns 1024.

What does =AVERAGEIF(B1:B10, ">0", C1:C10) calculate?

Answer: Average of C1:C10 where the corresponding B value is greater than 0

AVERAGEIF averages the values in the average_range (C1:C10) where corresponding cells in the criteria range (B1:B10) meet the condition.

In Excel, what does the formula =DATEIF(A1, B1, "M") calculate? (Note: DATEDIF)

Answer: The difference in months between two dates

DATEDIF with "M" as the unit returns the number of complete months between the start and end dates.

Which function extracts a substring from the middle of a text string given a start position and length?

Answer: MID

MID returns a specified number of characters from a text string, starting at a given position.

What does =SUMPRODUCT((A1:A10="Yes")*(B1:B10)) calculate?

Answer: Sum of B1:B10 where A1:A10 equals "Yes"

The Boolean array (A1:A10="Yes") produces 1s and 0s; multiplying by B1:B10 and summing effectively adds B values only where A equals "Yes".

Which formula correctly converts the text string "123" in cell A1 to a numeric value?

Answer: =VALUE(A1)

VALUE converts a text string that looks like a number into an actual numeric value.