Microsoft Excel Formulas 3 — Questions and Answers
Question 1: 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 2: 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 3: 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 4: 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 5: 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 6: 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 7: 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.
Microsoft Excel Formulas 3
Which function combines the results of multiple IF conditions without nesting?