Microsoft Excel Functions 4 — Questions and Answers
Question 1: Which function returns a value from a specific row and column within a given range?
- MATCH
- OFFSET
- INDEX (Correct answer)
- CHOOSE
Correct answer: INDEX
INDEX returns the value at the intersection of a specified row and column within a range.
Question 2: What does =SUMIF(A1:A10, ">100", B1:B10) calculate?
- Sum of B1:B10 where corresponding A values equal 100
- Sum of A1:A10 values greater than 100
- Sum of B1:B10 where corresponding A values are greater than 100 (Correct answer)
- Count of A1:A10 values greater than 100
Correct answer: Sum of B1:B10 where corresponding A values are greater than 100
SUMIF sums values in B1:B10 only for rows where the corresponding value in A1:A10 is greater than 100.
Question 3: Which function converts a number into text formatted as currency?
- DOLLAR (Correct answer)
- CURRENCY
- FORMAT
- TEXT
Correct answer: DOLLAR
DOLLAR converts a number to text in currency format, with optional decimal places, using the $ symbol.
Question 4: What does the EXACT function do?
- Rounds a number to exact decimal places
- Compares two text strings and returns TRUE if they are identical (case-sensitive) (Correct answer)
- Finds exact text matches in a range
- Returns the exact position of a substring
Correct answer: Compares two text strings and returns TRUE if they are identical (case-sensitive)
EXACT performs a case-sensitive comparison of two text strings, returning TRUE only if they match exactly.
Question 5: Which function returns the nth largest value in a data set?
- MAX
- MAXIFS
- LARGE (Correct answer)
- RANK
Correct answer: LARGE
LARGE returns the k-th largest value in a data set, where k is the second argument.
Question 6: What does the YEARFRAC function calculate?
- The number of years between two dates
- The fraction of the year represented by the number of days between two dates (Correct answer)
- The year portion of a date value
- The number of workdays in a year
Correct answer: The fraction of the year represented by the number of days between two dates
YEARFRAC returns the fraction of the year represented by the number of whole days between two dates, useful for financial calculations.
Question 7: Which function replaces all occurrences of a substring within a text string?
- FIND
- SEARCH
- REPLACE
- SUBSTITUTE (Correct answer)
Correct answer: SUBSTITUTE
SUBSTITUTE replaces all (or a specific) occurrences of a given text with new text within a string.
Microsoft Excel Functions 4
Which function returns a value from a specific row and column within a given range?