Microsoft Excel Formulas and Functions Application 2 — Questions and Answers
Question 1: What is the difference between relative and absolute cell references?
- Relative use letters, absolute use numbers
- Relative adjust when copied, absolute (with $) stay fixed (Correct answer)
- Absolute are faster
- No functional difference
Correct answer: Relative adjust when copied, absolute (with $) stay fixed
Relative references adjust when copied; absolute references ($A$1) remain fixed.
Four types: Relative (A1), Absolute ($A$1), Mixed ($A1 or A$1). Press F4 to cycle through types while editing.
Question 2: What does COUNTIFS do that COUNTIF cannot?
- Count text cells
- Apply multiple criteria across different ranges simultaneously (Correct answer)
- Count in multiple worksheets
- Count unique values
Correct answer: Apply multiple criteria across different ranges simultaneously
COUNTIFS evaluates multiple criteria across multiple ranges, counting only when ALL are met.
Criteria can use wildcards, comparison operators, and cell references. Each criteria_range must have the same dimensions.
Question 3: How does ROUND differ from TRUNC?
- ROUND works with negatives, TRUNC does not
- ROUND applies mathematical rounding, TRUNC removes decimals without rounding (Correct answer)
- They produce identical results
- TRUNC is more precise
Correct answer: ROUND applies mathematical rounding, TRUNC removes decimals without rounding
ROUND follows rounding rules (5 rounds up); TRUNC simply cuts off digits.
ROUND(2.65,1)=2.7, TRUNC(2.65,1)=2.6. For negatives: INT(-2.3)=-3, TRUNC(-2.3)=-2.
Question 4: What does the TEXT function do?
- Converts text to numbers
- Formats a number as text using a format code (Correct answer)
- Finds text in a cell
- Compares text strings
Correct answer: Formats a number as text using a format code
TEXT converts a numeric value to text with a specific format.
Examples: TEXT(TODAY(),'MMMM D, YYYY'), TEXT(0.856,'0.0%'). Result is text, not a number.
Question 5: How does AVERAGEIFS calculate its result?
- Averages all criteria values
- Averages values where all corresponding criteria are met (Correct answer)
- Returns average of COUNTIFS and SUMIFS
- Averages top and bottom only
Correct answer: Averages values where all corresponding criteria are met
AVERAGEIFS averages values in a range only where ALL corresponding criteria are satisfied.
Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, ...). Returns #DIV/0! if no cells meet all criteria.
Question 6: What does the UNIQUE function do in Excel 365?
- Removes duplicate formatting
- Extracts distinct values from a range as a dynamic array (Correct answer)
- Counts unique values
- Merges duplicate rows
Correct answer: Extracts distinct values from a range as a dynamic array
UNIQUE returns a dynamic array of distinct values, removing duplicates.
UNIQUE(array, [by_col], [exactly_once]). Set exactly_once=TRUE for values appearing only once. Combines with SORT and FILTER.
Microsoft Excel Formulas and Functions Application 2
What is the difference between relative and absolute cell references?