Level 4 · A price histogram with COUNTIFS

An average hides the shape of the data. The 60 products on this sheet average $84.09. That could mean most of them cost about $84, or that many cost $20 and a few cost $250. A histogram shows which: it splits the range of values into equal bins and draws one column per bin, as tall as the number of values that fall in it.

Bins: lower bound in, upper bound out

Each band on the sheet has a lower bound (G) and an upper bound (H), both $50 apart. A price belongs to a band when it is at least the lower bound and below the upper bound. That rule puts every price in exactly one bin, including a price that sits exactly on a boundary.

Band	Condition
$0–50	price >= 0 and price < 50
$50–100	price >= 50 and price < 100
…	…
$250–300	price >= 250 and price < 300

COUNTIFS with two conditions on one column

COUNTIFS takes pairs of (range, criteria) and counts the rows that pass all of them. Both pairs can test the same column:

I2   =COUNTIFS($D$2:$D$61,">="&G2,$D$2:$D$61,"<"&H2)

The & glues the operator to the number in the cell, building ">=0" and "<50". Keeping the bounds in cells means you can try $25 bands later by changing two columns, not six formulas. Then the check:

I8   =SUM(I2:I7)

I8 must be 60, one per product. Fewer means a price fell outside every bin (raise the top bound); more means a price counted twice (an <= crept in).

Making it in Excel

Select F1:F7, hold Ctrl (Cmd on a Mac) and select I1:I7, then Insert → Charts → Column. In Chart Design → Add Chart Element, add axis titles (“Price band”, “Products”). A histogram’s columns traditionally touch: right-click a column → Format Data Series → Gap Width 0%.

Excel 2016 and later can also build it in one step from the raw prices: select D1:D61, then Insert → Charts → Statistic → Histogram, and set the bin width to 50 under Format Axis. Its bins work the other way around, upper bound in, lower bound out, so a price of exactly 50.00 would land in the first bin there. Google Sheets has a Histogram chart type too. The COUNTIFS table is worth knowing because you control the bins and can reuse the counts in other formulas.

What the chart shows

The shape is lopsided. 26 of the 60 products, 43%, cost under $50, and the columns fall to 12 between $50 and $100, then 9 and 10 in the next two bands. Above $200 there are only 3 products: 2 between $200 and $250 and 1 between $250 and $300. This range is a cheap-items business with a thin premium tail. The tail also explains the average: half the products cost $68.99 or less (the median), but the few expensive ones pull the average up to $84.09. If Mei wants a “better” tier, the gap above $200 is where it would go.

Common mistakes

• Overlapping bins (>= and <= on both ends): boundary values count twice.
• Bins of different widths. A $0–50 bin next to a $50–300 bin makes the second look busier only because it is wider. Keep widths equal.
• Forgetting the &. ">=G2" is the text “greater than or equal to G2”, which matches nothing, and the bin count is 0.
• Not checking the total. I8 is a one-second test that catches all of the above.