Analyze ad watch-time with Excel pivots

A flat file is provided containing the following columns: Date (an Excel date without a time zone), UserID, AdID, WatchSeconds (an integer $\ge$ 0), and Clicks (an integer $\ge$ 0). With Excel alone (Power Query is not allowed), carry out each of the following tasks exactly: (1) Insert helper columns into the source data: Weekday = $\text{WEEKDAY}([@Date], 2)$ with Monday equal to 1; ISOWeek = $\text{ISOWEEKNUM}([@Date])$; ISOYear = $\text{YEAR}([@Date] + 4 - \text{WEEKDAY}([@Date], 2))$. State the reason ISOYear is necessary when dates cross year boundaries. (2) Create a PivotTable that finds the one calendar date with the greatest number of clicks per watch-minute. This ratio has to be calculated as $\frac{\text{SUM Clicks}}{\text{SUM WatchSeconds} / 60}$ by means of a PivotTable Calculated Field (give the precise formula string for that field). Break ties by selecting the earliest Date, and explain how you would make that tie-break deterministic within Excel. (3) Construct a second PivotTable that sums WatchSeconds grouped by ISOYear and ISOWeek, then returns the week having the largest total WatchSeconds. When two weeks are tied, choose the later week, ordering first by ISOYear and then by ISOWeek. (4) Identify two pitfalls that might lead to incorrect results when WEEKNUM/WEEKDAY are used instead of ISO week logic (for instance, weeks that cross December/January or week starts that depend on locale). Provide the two results: the Top Date and the Top ISO week (formatted as ISOYear-W##), and describe, step by step, the exact clicks/watch-minute and total WatchSeconds calculations you would carry out in the PivotTables.

Overview: This question assesses a data scientist's skill with Excel PivotTables, handling of dates and ISO weeks, aggregation and ratio measures (clicks per watch-minute), and deterministic tie-breaking methods that yield reproducible outcomes.