GMetrix Exam GMetrix Excel: Formulas and Functions 5 — Questions and Answers
Question 1: What is the purpose of the XLOOKUP function compared to VLOOKUP?
- XLOOKUP is faster but less accurate
- XLOOKUP can search in any direction and returns a range instead of a column index (Correct answer)
- XLOOKUP only works with horizontal data
- XLOOKUP requires sorted data
Correct answer: XLOOKUP can search in any direction and returns a range instead of a column index
XLOOKUP searches in any direction, returns an array, and doesn't require a col_index_num like VLOOKUP.
Question 2: Which function converts a text string to all uppercase letters?
- PROPER
- UPPER (Correct answer)
- CAPS
- TEXT
Correct answer: UPPER
UPPER converts every letter in a text string to uppercase.
Question 3: What does =CHOOSE(2, "Apple", "Banana", "Cherry") return?
- Apple
- Banana (Correct answer)
- Cherry
- 2
Correct answer: Banana
CHOOSE returns the value from the list that corresponds to the index number; index 2 returns the second value.
Question 4: Which statistical function returns the most frequently occurring value in a range?
- MEDIAN
- AVERAGE
- MODE (Correct answer)
- FREQUENCY
Correct answer: MODE
MODE returns the value that appears most often in a data set.
Question 5: What does the formula =A1&" "&B1 do?
- Adds A1 and B1 together
- Joins the text values of A1, a space, and B1 (Correct answer)
- Compares A1 and B1
- Returns an error if A1 or B1 is empty
Correct answer: Joins the text values of A1, a space, and B1
The ampersand & operator concatenates text values, combining A1, a space, and B1 into one string.
Question 6: Which function returns the absolute (positive) value of a number?
- POSITIVE
- ABS (Correct answer)
- SIGN
- MOD
Correct answer: ABS
ABS returns the absolute value of a number, removing any negative sign.
Question 7: What does =COUNTIFS(A1:A10,">5",B1:B10,"<10") do?
- Counts cells in A1:A10 greater than 5
- Counts rows where A is greater than 5 AND B is less than 10 (Correct answer)
- Counts cells matching either condition
- Returns an error with two ranges
Correct answer: Counts rows where A is greater than 5 AND B is less than 10
COUNTIFS counts rows where all specified conditions are true simultaneously, using AND logic across multiple criteria.
GMetrix Exam GMetrix Excel: Formulas and Functions 5
What is the purpose of the XLOOKUP function compared to VLOOKUP?