18.10 MathCo and FedEx India

M67. What is the difference between VLOOKUP and HLOOKUP, and their limitations?

Reported for: MathCo [S15]

VLOOKUP searches vertically in the first column of a range; HLOOKUP searches horizontally in the first row (e.g. months across the top). Both share the same limitations: they can't look left/up, use a hard-coded index number, default to approximate match and return only the first match. XLOOKUP or INDEX-MATCH replace both.

M68. Build a pivot of total sales by month and region, with each region's % contribution per month. Some dates are mm/dd/yyyy and some dd-mm-yyyy – how do you standardise?

Reported for: MathCo [S15]

First standardise dates: find text dates with ISNUMBER, convert US-style text with DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)) and dd-mm-yyyy text with DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)), or use Power Query › Change Type › Using Locale. Then pivot: Order Date grouped by Months in Rows, Region in Columns, Sales in Values shown as % of Row Total – each month row sums to 100%.

M69. Find the top 3 products by revenue in each region and compute month-over-month growth.

Reported for: MathCo [S15]

Pivot with Region then Product in Rows, Sum of Revenue, and Value Filters › Top 10 › 3 Items on Product (applies within each region). For MoM, add Month to Columns and a second Revenue field shown as % Difference From previous month. In Microsoft 365, TAKE(SORT(FILTER(...),2,-1),3) per region gives the same list with formulas.

M70. A dataset has repeated customer IDs, missing fields and outliers in order amounts. How do you prepare it?

Reported for: MathCo [S15]

Decide whether repeated IDs are true duplicates (same order repeated) or legitimate repeat purchases – remove only exact duplicate orders. Profile missing fields and handle them by business meaning (flag, "Unknown", fill down). Flag outliers with IQR, verify them, and decide to keep, cap or exclude them in a documented way. Reconcile row counts and totals at the end.

M71. Design an Excel dashboard for product metrics such as DAU, churn rate, revenue and AOV. How would you refresh it and ensure correctness?

Reported for: MathCo [S15]

Data sheets fed by Power Query (refreshable); a Calc sheet with clear KPI definitions – DAU = distinct active users per day, churn = users lost ÷ users at start of period, AOV = revenue ÷ orders; KPI cards with comparison vs previous period; line charts for DAU and revenue trend, bar for churn by segment; slicers for date and segment. Ensure correctness with cross-check formulas and reconciliation to source totals; refresh with Refresh All; document definitions.

M72. How do you extract part of a string, such as the city from "City-Zip"?

Reported for: MathCo [S15]

=LEFT(A2,FIND("-",A2)-1) returns "Pune" from "Pune-411038"; =TEXTBEFORE(A2,"-") in Microsoft 365; or Text to Columns / Flash Fill / Power Query Split Column by delimiter.

M73. Explain SUMIFS/COUNTIFS, OFFSET and INDIRECT.

Reported for: MathCo [S15]

SUMIFS/COUNTIFS sum or count with multiple AND conditions. OFFSET returns a range shifted from a start cell by rows/columns with a given size – useful for rolling windows but volatile. INDIRECT converts text into a reference, e.g. =SUM(INDIRECT("'"&B1&"'!G:G")) sums column G of the sheet named in B1 – flexible but volatile and it breaks silently if sheets are renamed, so use sparingly.

M74. When do you use a line chart and when a pie chart?

Reported for: FedEx India [S16]

A line chart shows how a value changes over an ordered sequence, usually time – daily sales through Ganeshotsav. A pie chart shows parts of one whole at one point in time and works only with a few categories (ideally 2–5) that add up to 100% – FreshGo vs MarketRush share of orders. Never use a pie for trends or for many small categories.