Task 3 — Plotting temperature against users

What this task is really about

You've got the 24-month summary table from Task 2. Task 3 asks you to plot it — two lines, one for monthly average temperature, one for monthly average user count, against month on the x-axis. The point of the chart is to let someone see, at a glance, whether users follow temperature (spoiler: they mostly do — warmer months have more riders, with 2012 sitting consistently above 2011 because the scheme grew).

The tricky bit isn't getting a chart onto the sheet — that's one line. The tricky bit is that the two series live on wildly different scales. Monthly average temperatures sit between roughly 8 and 31 (degrees Celsius), while monthly average user counts run between roughly 1,200 and 7,300 with the default total dropdown (the casual and registered options give different ranges, but all of them dwarf the temperature scale). Plot both on the same y-axis and the temperature line flatlines at the bottom while the user line dominates. The fix is a secondary axis; a second y-axis on the right side of the chart, with its own scale, used only for one of the series. That's most of what the Task 3 code is doing: building a plain line chart, then telling one of its series to use the secondary axis.

How to read the exam wording

"A chart which plots the average number of users against the average temperature in each month" is mildly ambiguous. It could mean a scatter plot with temperature on the x-axis and users on the y-axis (an associations chart). Or it could mean a time series — months on the x-axis, two lines showing how each value moves over time. The official solution goes with the time-series reading, and the solutions workbook even says at the bottom: "A SCATTER PLOT WOULD ALSO BE AN APPROPRIATE REPRESENTATION OF THIS DATA". You'd get the marks for either; the official solution goes with a line chart because it shows the seasonal pattern more clearly.

Before you write any code, picture what the chart needs to look like. A line chart on the Questions sheet, sitting under the monthly-averages table, about twenty rows tall and seven columns wide. Two line series, labelled "Temperature" and "Users". X-axis with months from 01/2011 to 12/2012. Temperature on the left y-axis, Users on the right y-axis. A title at the top, and a legend at the bottom so you know which line is which.

The minimum-viable, click-through version

Manual chart: select the 25-row-by-3-column block on the Questions sheet; the header row plus the 24 monthly rows, using whichever source you built in Task 2 (the pivot output or the LAMBDA spill) → Insert → 2-D Line. Right-click the second series → Format Data Series → Secondary Axis. Edit the title. Drag the legend to the bottom. That's three minutes.

The script does the same thing but reproducibly. The real reason to script it is that the chart's placement and the axis settings are fiddly; re-clicking after every change to the data is what scripts avoid.

Two ways to source the chart data

The official solution has two functions for the chart itself — Q3chartFormula and Q3chartPTable, and main calls the formula version:

// let chart = Q3chartPTable(Qs);
let chart = Q3chartFormula(Qs);
Q3chartFormat(chart);

The comment-out tells you the author's intent: pick one. Both produce a chart. What changes is where the chart reads its numbers from.

Formula source:

function Q3chartFormula(Qs: ExcelScript.Worksheet) {
    let data = Qs.getCell(19, 5).getSurroundingRegion();
    let chart = Qs.addChart(ExcelScript.ChartType.line, data);
    chart.setPosition("B53", "H72");
    return chart;
}

getCell(19, 5) resolves to F20 (0-indexed: row 19 = row 20, col 5 = col F); the cell where the script writes the LAMBDA call. (Same script-vs-workbook caveat as Task 2: the script's getCell(19, 5) is F20, but the solutions workbook on file actually shows the LAMBDA spill starting at F23. If your output lands at F20 you've followed the code correctly.) getSurroundingRegion() expands that single cell to the whole contiguous filled block; the header row plus the 24 data rows. The chart takes that block and reads columns in order: the first column becomes the category axis, each remaining column becomes a series.

Pivot source:

function Q3chartPTable(Qs: ExcelScript.Worksheet) {
    let pTable = Qs.getPivotTable("Q2MonthlyAverages");
    let data = pTable.getLayout().getRange();
    let chart = Qs.addChart(ExcelScript.ChartType.line, data);
    chart.setPosition("B53", "H72");
    return chart;
}

Same mechanics, different source. getPivotTable looks up the pivot by name; getLayout().getRange() returns the rectangular range the pivot currently occupies.

Which one to pick? The pivot source re-reads the pivot cache, which doesn't update when the dropdown changes unless you refresh. The formula source reads the LAMBDA output, which is live. If you built only the pivot in Task 2 (and nothing else), use the pivot source. If you built the LAMBDA, prefer the formula source because the chart will react to the dropdown without a refresh step. The official script reflects this trade-off by defaulting to the formula version.

Methods used: getCell, getSurroundingRegion, addChart, setPosition, getPivotTable, getLayout, getRange; see range.pdf, table_chart.pdf, and pivottable.pdf.

A chart series is one line in the chart; the points plus the line connecting them. Each column of numeric data gives you one series. For this chart there are two series: the temperature column (series index 0) and the users column (series index 1). An axis is the line running along the edge of the plot area with tick marks; the category axis is the x-axis in a line chart (the months here), and the value axis is the y-axis.

A note on setPosition. It takes two cell addresses as strings and pins the chart between them; the top-left corner sits on the first cell, the bottom-right on the second. "B53" to "H72" covers columns B through H (7 columns) and rows 53 through 72 (20 rows), which is a comfortable landscape aspect ratio. You can move it later by clicking and dragging in the GUI; the script just gives it a starting home.

The formatting function

Q3chartFormat does the interesting work. Four things: title the chart, put the users series on a secondary axis, move the legend, and configure the x-axis. Take them in order.

Title

chart.getTitle().setText("Temperature vs. Users");

getTitle() returns the chart's title object (charts always have one, though it can be hidden); setText sets what it shows. The title sits above the plot area by default.

Secondary axis; the fix for the scale mismatch

chart.getSeries()[1].setAxisGroup(ExcelScript.ChartAxisGroup.secondary);

This raises a question the official solution doesn't address: which series should go on the secondary axis? It's a style call, not a correctness one. The official puts users on the right (because series index 1 is users) and temperature on the left. An equally valid choice would be temperature on the right (maybe because you want the chart to read as "a visualization of users, with temperature for context"). The technique is the same either way.

Without any further setup, Excel will label the left axis with temperature's scale and the right axis with users' scale. A final polish — not in the official code — would be to title the primary axis so the reader knows which scale belongs to temperature: chart.getAxes().getValueAxis().getTitle().setText("Temperature (°C)") (getValueAxis() takes no arguments — it returns the primary value axis). Titling the secondary value axis is more fiddly in ExcelScript and isn't needed for marks. Consider it if you have time, skip it if not.

Legend position

chart.getLegend().setPosition("bottom");

The default legend position in Excel is right-of-plot, which works for short series lists but crowds the chart when two axes are active. "Bottom" frees up horizontal space. setPosition accepts string values like "top", "bottom", "left", "right"; the ExcelScript docs enumerate them under ChartLegendPosition.

Aside on the comment in the official script: there's a line reading // Remove legend immediately above chart.getLegend().setPosition("bottom"). The code doesn't actually remove it — setVisible(false) would do that. The comment is misleading. The actual behaviour is move-to-bottom.

Category axis configuration

The longest chunk in this function:

let xAxis = chart.getAxes().getCategoryAxis();
xAxis.setCategoryType(ExcelScript.ChartAxisCategoryType.dateAxis);
xAxis.getTitle().setVisible(false);
xAxis.setTickLabelPosition(ExcelScript.ChartAxisTickLabelPosition.low);
xAxis.setMajorUnit(2);

Take the methods one by one.

getAxes() returns the axis collection; getCategoryAxis() reaches the x-axis (for line charts it's the horizontal one).

setCategoryType(ExcelScript.ChartAxisCategoryType.dateAxis) is the key line. The MonthYear column stores real Excel date values with a mm/yyyy display format, so Excel usually picks up that the category axis is a date axis on its own. Setting dateAxis explicitly is the defensive move — it forces Excel to treat the values as real dates spaced proportionally on a timeline rather than as plain text labels with equal spacing, in case the auto-detection ever misses. With daily data this matters a lot; with evenly-spaced monthly data it matters less visually but is still the correct choice, and it's what lets setMajorUnit(2) mean "every 2 months".

xAxis.getTitle().setVisible(false) hides the x-axis title. By default it inherits the source range's first column header — "Date" if you used the formula source, or whatever the pivot's row-field header is if you used the pivot source, and 24 date labels along the bottom make the category self-explanatory, so the extra word is clutter.

setTickLabelPosition(ExcelScript.ChartAxisTickLabelPosition.low) pins the tick labels at the bottom of the plot. If you leave this alone Excel sometimes decides to draw the labels next to the axis origin, which on a chart with a secondary axis can put the date labels floating in mid-plot. Forcing low keeps them glued to the bottom edge regardless.

setMajorUnit(2) sets the gap between labelled tick marks. For a date axis the unit depends on the base time unit — here, months, so 2 means "one label every two months". With 24 months of data you get 12 labels, which is readable. Without this, Excel auto-picks — often one per month (cluttered) or one per year (too sparse).

Methods used: getTitle, setText, getSeries, setAxisGroup, getLegend, setPosition, getAxes, getCategoryAxis, setCategoryType, setVisible, setTickLabelPosition, setMajorUnit; see table_chart.pdf. The enums ChartAxisGroup, ChartAxisCategoryType, ChartAxisTickLabelPosition live in the ExcelScript API docs; 06ExcelScriptAPI.pdf introduces the pattern.

Things that break

The single most common failure: calling Q3chartFormat(chart) before any series exists. If your source range is accidentally a single cell, addChart still makes a chart but with zero series, and then chart.getSeries()[1] throws because index 1 doesn't exist. Check the source range is what you think before formatting.

The second most common: the pivot isn't refreshed, so the chart reads stale numbers. Fix by running pTable.refresh() before building the chart, or by switching to the formula source.

The third: the user types a different dropdown option, the pivot doesn't refresh, and the users line now reflects the old dropdown. Same fix as above — refresh the pivot, or source the chart from the formula.

If this still feels shaky

02Charts.pdf is the main lecture on chart construction and why secondary axes exist. table_chart.pdf is the method cheatsheet — scroll to the Chart section for the full list of getters and setters used here. 06ExcelScriptAPI.pdf walks through enums in general, which is useful if the ExcelScript.ChartAxisGroup.secondary pattern still feels opaque. If you want the clearest reason behind each formatting choice, open the solutions workbook and temporarily delete each formatting line one at a time — you'll see exactly what each one was doing.