Microsoft Excel Functions Questions and Answers 2 — Questions and Answers

Question 1: What does the COUNTIF function do in Excel?

Counts the number of cells in a range that meet a single condition (Correct answer)
Counts all non-empty cells in a range
Counts the number of unique values in a range
Counts cells that contain formulas

Correct answer: Counts the number of cells in a range that meet a single condition

COUNTIF(range, criteria) counts cells that match a specified condition, such as counting how many cells contain a value greater than 100 or matching a specific text.

COUNTIF(range, criteria) is a conditional counting function. The criteria can be a number, text, expression, or cell reference. Examples: =COUNTIF(A1:A100,">50") counts cells greater than 50, =COUNTIF(A1:A100,"Apple") counts cells containing "Apple". Wildcards are supported: "*" matches any characters, "?" matches a single character. For multiple conditions, use COUNTIFS. COUNTA counts all non-empty cells without conditions, while COUNTBLANK counts empty cells. COUNTIF is case-insensitive for text matching.

Question 2: Which function would you use to find the largest value in a range of cells?

MAX (Correct answer)
LARGE
HIGH
TOP

Correct answer: MAX

MAX returns the largest numeric value in a range. For example, =MAX(A1:A100) returns the highest value from that range.

MAX(number1, [number2], ...) returns the largest value from the provided arguments or ranges. It ignores logical values, text, and empty cells within ranges. For example, =MAX(10,25,3,47,12) returns 47. Related functions include MIN (smallest value), LARGE (k-th largest value, e.g., LARGE(range,2) for the second largest), and MAXIFS (largest value meeting conditions). MAXA includes logical values (TRUE=1, FALSE=0) in the evaluation, unlike MAX which ignores them.

Question 3: What does the VLOOKUP function's fourth argument (range_lookup) control?

Whether to find an exact match or an approximate match (Correct answer)
The number of columns to search
Whether the search is case-sensitive
The direction of the lookup

Correct answer: Whether to find an exact match or an approximate match

The fourth argument controls match type: FALSE (or 0) requires an exact match, while TRUE (or 1) allows an approximate match using a sorted lookup column.

VLOOKUP's range_lookup parameter is critical for correct results. When set to FALSE (exact match), the function searches for the precise lookup value and returns #N/A if not found. When set to TRUE or omitted (approximate match), the lookup column MUST be sorted in ascending order, and the function returns the closest value less than or equal to the lookup value. Most users need exact match (FALSE) for database-style lookups. Approximate match is useful for tax brackets, grade scales, or commission tiers where you need to find which range a value falls into.

Question 4: Which function calculates the average of cells that meet a specific condition?

AVERAGEIF (Correct answer)
AVGIF
AVERAGE
SUMIF

Correct answer: AVERAGEIF

AVERAGEIF(range, criteria, [average_range]) calculates the arithmetic mean of cells that satisfy the specified condition.

AVERAGEIF(criteria_range, criteria, [average_range]) computes the average of cells meeting a condition. If average_range is omitted, it averages the criteria_range itself. For example, =AVERAGEIF(A1:A100,"East",B1:B100) averages values in column B where column A equals "East". For multiple conditions, use AVERAGEIFS. Unlike AVERAGE which includes all numeric cells, AVERAGEIF lets you focus on a specific subset. Cells with zero are included in the average; only truly empty cells or text cells are excluded from the calculation.

Question 5: What does the CONCATENATE function do in Excel?

Joins two or more text strings into one string (Correct answer)
Splits a text string into separate cells
Compares two text strings for equality
Removes duplicate characters from text

Correct answer: Joins two or more text strings into one string

CONCATENATE joins multiple text strings into a single string. It has been largely replaced by CONCAT and TEXTJOIN in newer Excel versions.

CONCATENATE(text1, [text2], ...) joins up to 255 text strings into one. For example, =CONCATENATE("Hello"," ","World") returns "Hello World". In Excel 2019 and Microsoft 365, CONCAT replaces CONCATENATE with the added ability to accept ranges (not just individual cells). TEXTJOIN goes further by allowing a delimiter and an option to ignore empty cells. The ampersand (&) operator provides the same functionality inline: ="Hello"&" "&"World". CONCATENATE does not add separators automatically; you must include them as arguments.

Question 6: Which Excel function removes leading and trailing spaces from a text string?

TRIM (Correct answer)
CLEAN
STRIP
COMPACT

Correct answer: TRIM

TRIM removes all leading spaces, trailing spaces, and reduces multiple consecutive spaces between words to a single space.

TRIM(text) removes extra space characters from text: all leading and trailing spaces are removed, and multiple spaces between words are reduced to single spaces. This is essential for cleaning imported data where invisible extra spaces can cause VLOOKUP failures, IF comparisons to return incorrect results, and COUNTIF to miss matches. TRIM only handles regular space characters (ASCII 32). For non-breaking spaces (ASCII 160, common in web data), use SUBSTITUTE(A1,CHAR(160),"") or combine TRIM with CLEAN, which removes non-printable characters.

Microsoft Excel Functions Questions and Answers 2

What does the COUNTIF function do in Excel?