Microsoft Excel Formulas — Questions and Answers
Question 1: Which function returns the position of a value within a range?
- FIND
- MATCH (Correct answer)
- SEARCH
- INDEX
Correct answer: MATCH
MATCH returns the relative position of a value within a one-dimensional range.
Question 2: What does the formula =IFERROR(A1/B1, "Error") return when B1 is 0?
- 0
- #DIV/0!
- Error (Correct answer)
- FALSE
Correct answer: Error
IFERROR returns the second argument whenever the first argument produces any error, including #DIV/0!.
Question 3: Which of the following is a valid absolute cell reference in a formula?
- A1
- $A1
- $A$1 (Correct answer)
- A$
Correct answer: $A$1
$A$1 locks both the column and row so the reference doesn't change when copied.
Question 4: What result does =MOD(17, 5) return?
Correct answer: 2
MOD returns the remainder after division; 17 ÷ 5 = 3 remainder 2.
Question 5: Which function counts only the cells in a range that contain numbers?
- COUNTA
- COUNTIF
- COUNT (Correct answer)
- COUNTBLANK
Correct answer: COUNT
COUNT tallies only numeric values, ignoring text, blanks, and errors.
Question 6: The formula =LEFT("PracticeTest", 8) returns which string?
- Practice (Correct answer)
- TestGeek
- PracticeT
- racticeT
Correct answer: Practice
LEFT extracts 8 characters from the start of the text string, yielding "Practice".
Question 7: What happens when you enter a formula that creates a circular reference in Excel?
- Excel deletes the formula automatically
- Excel returns zero by default
- Excel displays a warning and may return 0 or an error (Correct answer)
- Excel solves it using Goal Seek
Correct 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.
Question 8: Which function combines the results of multiple IF conditions without nesting?
- SWITCH
- IFS (Correct answer)
- CHOOSE
- CASE
Correct answer: IFS
IFS evaluates multiple condition-value pairs in order and returns the value for the first TRUE condition.
Question 9: What does =CONCATENATE("Hello", " ", "World") return?
- HelloWorld
- Hello World (Correct answer)
- Hello World
- "Hello" "World"
Correct answer: Hello World
CONCATENATE joins text strings in order; the space in the middle argument produces "Hello World".
Question 10: The ROUND function =ROUND(3.567, 2) returns which value?
- 3.5
- 3.56
- 3.57 (Correct answer)
- 4.0
Correct answer: 3.57
ROUND with 2 decimal places rounds 3.567 to 3.57 because the third decimal (7) is ≥ 5.
Question 11: Which formula correctly calculates a weighted average for values in A1:A3 with weights in B1:B3?
- =AVERAGE(A1:A3)
- =SUMPRODUCT(A1:A3,B1:B3)/SUM(B1:B3) (Correct answer)
- =SUM(A1:A3)/SUM(B1:B3)
- =AVERAGEIF(A1:A3,B1:B3)
Correct 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.
Question 12: What does the & operator do in an Excel formula?
- Performs logical AND
- Concatenates text strings (Correct answer)
- Adds two numbers
- Compares two values
Correct answer: Concatenates text strings
The & operator joins (concatenates) text values, functioning identically to the CONCATENATE function.
Question 13: Which function returns the current date and time, updating each time the workbook recalculates?
- TODAY()
- DATE()
- NOW() (Correct answer)
- TIME()
Correct answer: NOW()
NOW() returns the current date and time as a serial number that recalculates whenever the worksheet changes.
Question 14: In the formula =VLOOKUP(D2, A2:C10, 3, FALSE), what does the argument FALSE specify?
- Return a false value if not found
- Require an exact match (Correct answer)
- Search from bottom to top
- Sort the lookup column descending
Correct answer: Require an exact match
FALSE (or 0) as the fourth VLOOKUP argument forces an exact match, returning #N/A if none is found.
