GMetrix Exam GMetrix Excel: Formulas and Functions 3 — Questions and Answers
Question 1: What does the IFERROR function do?
- Highlights cells with errors
- Returns a custom value when a formula produces an error (Correct answer)
- Fixes formula errors automatically
- Deletes cells with errors
Correct answer: Returns a custom value when a formula produces an error
IFERROR evaluates a formula and returns a specified value instead of an error message if the formula produces an error.
Question 2: Which formula calculates the average of cells B1:B10 only where A1:A10 equals "Sales"?
- =AVERAGE(B1:B10,"Sales")
- =AVERAGEIF(A1:A10,"Sales",B1:B10) (Correct answer)
- =AVERAGEIFS(B1:B10,"Sales")
- =IF(A1:A10="Sales",AVERAGE(B1:B10))
Correct answer: =AVERAGEIF(A1:A10,"Sales",B1:B10)
AVERAGEIF calculates the average of a range based on a single condition in another range.
Question 3: What is the keyboard shortcut to enter an array formula in Excel?
- Ctrl+Enter
- Shift+Enter
- Ctrl+Shift+Enter (Correct answer)
- Alt+Enter
Correct answer: Ctrl+Shift+Enter
Ctrl+Shift+Enter enters an array formula and wraps it in curly braces {}.
Question 4: Which function returns the largest value in a set of values?
- LARGE
- MAX (Correct answer)
- UPPER
- TOP
Correct answer: MAX
MAX returns the maximum (largest) numeric value from a range or list of values.
Question 5: What does =TEXT(1234.5, "$#,##0.00") return?
- 1234.5
- $1234.50
- $1,234.50 (Correct answer)
- 1,234.50
Correct answer: $1,234.50
The TEXT function formats a number as text using the specified format code, adding a dollar sign and comma separator.
Question 6: Which function finds the second largest value in a range?
- MAX(range,2)
- LARGE(range,2) (Correct answer)
- SECOND(range)
- RANK(range,2)
Correct answer: LARGE(range,2)
LARGE(range, k) returns the k-th largest value, so LARGE(range, 2) returns the second largest.
Question 7: What symbol is used to make a cell reference absolute in Excel?
- #
- @
- $ (Correct answer)
- &
Correct answer: $
The dollar sign $ locks a row or column reference so it does not change when the formula is copied.
GMetrix Exam GMetrix Excel: Formulas and Functions 3
What does the IFERROR function do?