Level 5 · Price vs units sold on a scatter chart

A scatter chart plots one dot per item, with one number across (x) and another up (y). It is the chart for “do these two numbers move together?”. Unlike a line chart, the points are not connected and have no order: each dot is a product, not a moment in time.

Building the x and y columns

Here x is the list price in C and y is units sold in 2025. The export gives units by quarter, so the year needs adding up first:

H2   =SUM(D2:G2)

Filled down to H21, it gives each product’s year. Check one: the Cove Cast Iron Dutch Oven (row 6) sold 78 + 110 + 112 + 230 = 530 units.

In Excel, select C1:C21, hold Ctrl (Cmd on a Mac) and select H1:H21, then Insert → Charts → Scatter. Excel always puts the first selected column on the x-axis. Add both axis titles in Chart Design → Add Chart Element → Axis Titles; without them nobody knows which axis is the price.

One number for the relationship: CORREL

K1   =CORREL(C2:C21,H2:H21)

CORREL returns the correlation coefficient, from -1 to 1:

Value	Means
close to 1	as x rises, y rises, points near a straight line
close to 0	no straight-line relationship
close to -1	as x rises, y falls, points near a straight line

Both ranges must cover the same rows in the same order; CORREL pairs them by position. Excel can also draw the straight line through the dots: right-click a point → Add Trendline, and tick Display R-squared value if you want the fit on the chart. You meet CORREL again in chapter 9.

What the chart shows

K1 is -0.69: cheaper Kitchen products did sell more units in 2025. The five best sellers all cost under $61: the Vale Enamel Bowl Set (11.99, 616 units), Cove Cast Iron Dutch Oven (10.99, 530), Linden Enamel Serving Platter (19.99, 491), Willow Acacia Knife Block (53.99, 418) and Glen Stoneware Carafe (60.99, 384). The six products at $129 or more each sold between 51 and 258.

Look at the dots, not just the number. The Pine Cast Iron Carafe at 289.00 sold 55 units, about the same as the Hazel Stainless Dutch Oven at 109.00 (55). And the Linden platter sold nothing at all in the fourth quarter: it was discontinued, so its 491 is three quarters of sales. A single dot like that can move a correlation on only 20 products.

Correlation is not causation

The relationship is real, but it does not say that cutting the price of a $179 carafe would triple its sales. Cheap items here are bowls, platters and utensils that people buy often and in sets; expensive ones are things you buy once. Price and units both follow from what the product is. Before anyone cuts prices, compare products within one subcategory, or run a test: lower the price on a few items for a month and compare them with similar items that kept theirs.

Common mistakes

• A line chart for two numeric variables. It joins the dots in row order and draws a trend that isn’t there.
• Mismatched ranges. =CORREL(C2:C21,H3:H22) pairs each price with the next product’s units.
• Trusting r without the picture. One unusual dot can create or hide a correlation.
• Reporting correlation as cause. Say “go together”, not “drives”.