Excel Test for Entry-Level Data Analysts

How does the argument order in SUMIFS differ fundamentally from the legacy SUMIF function in Excel?

In target cell E2, calculate total revenue for the "Enterprise" segment in the "EMEA" region using SUMIFS.

A data analyst needs to count all customer records where the City column begins with "San" (e.g., San Francisco, San Diego, San Jose). Which criteria argument should be provided in COUNTIFS?

Which of the following comparison operators can be passed inside criteria strings for SUMIFS and COUNTIFS? (Select all that apply)

In target cell D2, count the number of closed tickets handled by the "Tier 2" team with Resolution Hours greater than 24 using COUNTIFS.

A data analyst needs to find the exact character position of an uppercase letter "D" in the code string "Data_ID_101". Which text function is strictly case-sensitive in Excel?

Which of the following capabilities are natively supported by the XLOOKUP function without requiring helper columns or nested functions? (Select all that apply)

In target cell B2, extract the 4-letter department code located starting at character position 5 from the Employee Code in A2 using the MID function.

What is the key analytical difference between the MAX function and the LARGE function?

Which of the following Excel functions are standard tools for cleaning and sanitizing imported text data? (Select all that apply)

In target cell D2, retrieve the Q3 revenue for the product specified in C2 using INDEX and MATCH on table A2:B4.

Cell A1 contains the text string " US-East " (two leading spaces, the 7-character string "US-East", and two trailing spaces). What does =LEN(TRIM(A1)) evaluate to?

Which of the following scenarios typically cause a spreadsheet formula to produce a #VALUE! error? (Select all that apply)

In target cell C2, retrieve the 2nd highest Monthly Active Users (MAU) count from range B2:B6 using the LARGE function.

What is the evaluated output of the formula =AND(5 > 2, "North" = "North", 10 < 4)?

Which statements are true regarding the rules and behavior of the AVERAGEIFS function? (Select all that apply)

In target cell B2, extract the 3-letter category prefix from the product SKU in cell A2 using the LEFT function.

In cell A1, a filename is stored as "quarterly_report.xlsx". Which formula dynamically extracts the file extension after the dot without hardcoding character counts?

When architecting scalable spreadsheet models for business reporting, which practices optimize maintainability and calculation speed? (Select all that apply)

In target cell E2, calculate the average lead response time in minutes for "Priority 1" tickets in the "FinTech" vertical using AVERAGEIFS.

In the function =MATCH(lookup_value, lookup_array, [match_type]), what does setting match_type to 0 accomplish?

Why do senior data analysts prefer INDEX & MATCH over legacy VLOOKUP for mission-critical workbooks? (Select all that apply)

In target cell D2, evaluate client eligibility. Return "Eligible" if Tenure (B2) is greater than or equal to 12 AND Monthly Spend (C2) is greater than or equal to 500; otherwise return "Review".

When a dynamic array formula like XLOOKUP or UNIQUE attempts to return a 3-column array, what happens if an adjacent cell in the output range is already populated with data?

Scenario Background: The Customer Success leadership team has identified accounts at risk of cancellation. To prepare the quarterly retention budget, the reporting analyst must calculate the total Annual Recurring Revenue (ARR) exposed among accounts that have "High Risk" status AND have logged more than 50 support tickets.

In target cell F2, calculate total ARR for accounts where Churn Risk (column D) is "High Risk" AND Support Tickets (column E) is strictly greater than 50 using SUMIFS.

Scenario Background: The distribution warehouse monitors stock replenishment triggers. Procurement policy states that an inventory SKU requires immediate reorder if Current Stock is strictly below Safety Stock, OR if Lead Time is 14 days or longer.

In target cell E2, determine the procurement status for SKU-101. Return "Reorder" if Stock (C2) is less than Safety Stock (D2) OR Lead Time (B2) is greater than or equal to 14; otherwise return "Sufficient".

What is the key diagnostic distinction between an #N/A error and a #REF! error in spreadsheet formulas?

Scenario Background: The commercial operations team is designing a performance improvement plan. The analyst needs to extract the lowest quarterly sales volume achieved across the regional sales team to establish the baseline minimum quota.

In target cell C2, retrieve the lowest sales achievement (1st smallest) across the regional sales team in range B2:B6 using the SMALL function.

Scenario Background: An advertising agency maintains a campaign performance table where Campaign Name is stored in column B and Campaign ID is in column A. Because traditional VLOOKUP cannot search to the left, the junior analyst must look up the Campaign ID using modern XLOOKUP.

In target cell E2, retrieve the Campaign ID from column A for the Target Name in D2 ("Autumn Surge") using XLOOKUP.

Scenario Background: An automated data extraction tool exports composite transaction keys formatted as CITY-TXNID-CURRENCY (e.g. "BOS-84920-USD"). To join this table with the general ledger, the analyst needs to extract only the 5-digit Transaction ID.

In target cell B2, extract the 5-digit Transaction ID located starting at character index 5 from the raw code in cell A2 using the MID function.