Microsoft Excel Formulas 5 — Questions and Answers
Question 1: What does =INDEX(A1:C5, 3, 2) return?
- The value in row 2, column 3
- The value in row 3, column 2 (Correct answer)
- The third row of the range
- An error because INDEX needs MATCH
Correct 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.
Question 2: Which operator is used to raise a number to a power in an Excel formula?
- *
- ^ (Correct answer)
- **
- POW
Correct answer: ^
The caret (^) is Excel's exponentiation operator; =2^10 returns 1024.
Question 3: What does =AVERAGEIF(B1:B10, ">0", C1:C10) calculate?
- Average of B1:B10 where values are positive
- Average of C1:C10 where the corresponding B value is greater than 0 (Correct answer)
- Sum of C1:C10 divided by count of B values over 0
- Average of all values greater than 0 in C1:C10
Correct 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.
Question 4: In Excel, what does the formula =DATEIF(A1, B1, "M") calculate? (Note: DATEDIF)
- The difference in days between two dates
- The difference in months between two dates (Correct answer)
- The difference in years between two dates
- The number of workdays between two dates
Correct 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.
Question 5: Which function extracts a substring from the middle of a text string given a start position and length?
- LEFT
- RIGHT
- MID (Correct answer)
- EXTRACT
Correct answer: MID
MID returns a specified number of characters from a text string, starting at a given position.
Question 6: What does =SUMPRODUCT((A1:A10="Yes")*(B1:B10)) calculate?
- Count of "Yes" values in A1:A10
- Sum of B1:B10 where A1:A10 equals "Yes" (Correct answer)
- Product of all "Yes" values and B values
- An error because text cannot be used in SUMPRODUCT
Correct 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".
Question 7: Which formula correctly converts the text string "123" in cell A1 to a numeric value?
- =TEXT(A1)
- =VALUE(A1) (Correct answer)
- =NUMBER(A1)
- =INT(A1)
Correct answer: =VALUE(A1)
VALUE converts a text string that looks like a number into an actual numeric value.
Microsoft Excel Formulas 5
What does =INDEX(A1:C5, 3, 2) return?