Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 93 ยท Statistics and Math

Frequency Distributions, Cumulative Frequency and Contingency Tables

Build frequency tables with FREQUENCY and COUNTIFS, cumulative frequency and histograms, contingency (cross-tab) tables with PivotTables, conditional probability, CAGR and point-biserial correlation.

Before you can describe a set of numbers, you usually count them: how many values fall in each range, and how many items fall in each combination of two categories. A frequency distribution counts values by class (age bands, score ranges); a cumulative frequency keeps a running total; a contingency table (cross-tab) counts items by two category columns, which is the starting point for conditional probability. This lesson also covers two small calculations that often come next: CAGR and the point-biserial correlation.

Excel cannot run on this site, so the formulas are real Excel but read-only. The TryIts are JavaScript stand-ins that reproduce the counts, percentages and correlation so you can check your own sheet. They are not Excel's code. For the summary statistics that go with these tables, see Statistics for Analysts.

Frequency with FREQUENCY

FREQUENCY(data_array, bins_array) counts how many values fall in each bin. The bins are the upper limits of the classes, and a value goes into the first bin whose limit it does not exceed (greater than the previous limit, up to and including this one). The result has one more item than the bins: the last item counts values above the highest bin.

TEXT
Data in A2:A21, bins 59, 69, 79, 89 in C2:C5
=FREQUENCY(A2:A21, C2:C5)

In Microsoft 365 and Excel 2021 the result spills into five cells automatically. In older versions select five cells (D2:D6), type the formula, and confirm with Ctrl+Shift+Enter to make it an array formula. A common error is selecting only four cells and losing the last count. Text and blank cells in the data are ignored.

Counting with COUNTIFS instead

COUNTIFS is easier to read and works for any boundaries, including "lower included, upper excluded":

TEXT
=COUNTIFS($A$2:$A$21, ">="&C2, $A$2:$A$21, "<"&D2)

where C2 is the class's lower limit and D2 its upper limit (Conditional Aggregates). Whichever you use, check that your counts add up to COUNT(data); a missing value usually means a gap or overlap between the classes.

Cumulative frequency and relative frequency

A cumulative frequency column is a running total of the counts, and a relative frequency is each count divided by the total:

TEXT
Count            E2   =COUNTIFS(...)
Relative         F2   =E2/SUM($E$2:$E$6)
Cumulative       G2   =SUM($E$2:E2)
Cumulative %     H2   =G2/SUM($E$2:$E$6)

Plot the counts as a column chart with Gap width 0 to get a histogram, or use Insert > Statistic Chart > Histogram, which bins automatically (you can set the bin width). The cumulative percentage drawn against the upper limits is an ogive (Scatter, Bubble, Frequency Polygon and Ogive).

JS Frequency, relative and cumulative columns

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Contingency tables (cross-tabs)

A contingency table counts observations by two categorical variables. Imagine 200 customers with a Channel (Online, Store) and an Outcome (Bought, Did not buy). The table shows the four combinations plus the row and column totals.

Fastest way: a PivotTable (PivotTable Basics). Put Channel in Rows, Outcome in Columns, and any text field (such as Customer ID) in Values as Count. Under Value Field Settings > Show Values As, choose % of Row Total, % of Column Total or % of Grand Total to turn counts into proportions without a single formula.

By formula, one cell per combination:

TEXT
=COUNTIFS($B$2:$B$201, $F2, $C$2:$C$201, G$1)

Fill it across and down, then add totals with SUM. You can test whether the two variables are related with a chi-square test: expected count for a cell is row total * column total / grand total, and =CHISQ.TEST(actual_range, expected_range) returns the p-value.

Reading a contingency table

Rows and columns answer different questions. For the example below, a row percentage answers "of the people who came online, what share bought?", while a column percentage answers "of the buyers, what share came online?". They are not the same number, and mixing them up is the most common mistake.

Conditional probability

The probability of an outcome given a category is a ratio of counts: P(Bought | Online) = (Online and Bought) / (all Online). In a sheet, =COUNTIFS(Channel,"Online",Outcome,"Bought")/COUNTIFS(Channel,"Online"). If P(Bought | Online) differs a lot from P(Bought | Store), the channel and the outcome are associated (this does not prove one causes the other).

JS A cross-tab, row percentages and conditional probability

Press Run (or Ctrl + Enter) to see the console output here.

No output.

CAGR: compound annual growth rate

CAGR is the steady yearly growth rate that would take a start value to an end value:

TEXT
=(End/Start)^(1/Years) - 1

For 100 growing to 180 in 5 years: =(180/100)^(1/5)-1 is about 12.5%. The built-in =RRI(5, 100, 180) gives the same result. Do not average the yearly percentages instead: growth compounds, so the arithmetic mean of yearly growth overstates it. =RATE(5,0,-100,180) also works (Financial Functions).

Point-biserial correlation

When one variable is continuous (test score) and the other is two-valued (passed a course: 1 or 0), the correlation between them is called the point-biserial correlation. In Excel it is simply CORREL with the two-valued column coded as 0 and 1:

TEXT
=CORREL(B2:B41, C2:C41)       ' B = score, C = 1 if studied, else 0

It equals the ordinary Pearson correlation on the 0/1 coding, so the sign tells you which group has the higher mean. See Correlation and Forecasting for interpretation.

JS CAGR and the point-biserial correlation

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. If you give FREQUENCY four bins, how many results does it return?

    FREQUENCY always returns one more count than the number of bins, because values above the highest bin need a place to go.
  2. 2. In a Channel by Outcome table, what does a row percentage show?

    A row percentage divides each cell by its row total, so it answers questions of the form "given this row, how often is each outcome?"
  3. 3. Which formula gives CAGR for 100 growing to 180 over 5 years?

    CAGR is the constant rate that compounds start into end, which is (End/Start)^(1/Years) - 1. Averaging yearly rates overstates growth.
  4. 4. How do you compute a point-biserial correlation in Excel?

    The point-biserial coefficient is Pearson's r on a variable coded 0/1, which is exactly what CORREL computes.

Summary

  • FREQUENCY(data, bins) counts by upper limits and returns one extra item for values above the last bin; COUNTIFS handles any boundaries.
  • Cumulative frequency is a running SUM; divide by the total for relative and cumulative percentages.
  • A contingency table counts two categories together; a PivotTable with Show Values As gives row, column and grand-total percentages.
  • Conditional probability is a ratio of counts: P(A | B) = count(A and B) / count(B).
  • CAGR is (End/Start)^(1/Years)-1 (or RRI); point-biserial correlation is CORREL with 0/1 coding.

Next: Outliers, Normalization and Linear Interpolation.