Histogram and frequency analysis in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate frequency distribution using FREQUENCY function

Given the data values in cells A2:A11 and bins in cells B2:B6, which formula entered in C2 and confirmed with Ctrl+Shift+Enter will correctly calculate the frequency distribution?

Excel

Data: A2:A11 = {3, 7, 8, 5, 12, 14, 21, 13, 18, 20}
Bins: B2:B6 = {5, 10, 15, 20, 25}

A =FREQUENCY(A2:A11, B2:B6)

B =COUNTIFS(A2:A11, ">" & B2:B6)

C =COUNTIF(A2:A11, "<=" & B2:B6)

D =FREQUENCY(B2:B6, A2:A11)

💡 Hint

Remember FREQUENCY(data_array, bins_array) counts how many data points fall into each bin range.

The FREQUENCY function syntax is FREQUENCY(data_array, bins_array). Option A correctly uses data in A2:A11 and bins in B2:B6. Option A reverses the arguments, causing wrong output. Options C and D use COUNTIF(S) which do not produce frequency arrays for bins.

❓ Function Choice

intermediate

Choose the best function to create a histogram in Excel

You want to create a histogram that groups sales data into ranges and shows counts for each range. Which Excel function is best suited to calculate the counts for each bin?

A AVERAGEIFS

B VLOOKUP

C FREQUENCY

D SUMIFS

💡 Hint

Think about a function that counts how many values fall into specified ranges.

FREQUENCY is designed to count how many data points fall into each bin range, perfect for histograms. SUMIFS sums values, AVERAGEIFS calculates averages, and VLOOKUP searches for values, so they are not suitable here.

❓ data_analysis

advanced

Analyze frequency output array length

If you use the formula =FREQUENCY(A2:A10, B2:B5) in Excel, how many elements will the resulting frequency array contain?

Excel

Data range: A2:A10 (9 values)
Bins range: B2:B5 (4 values)

A 4

B 10

C 9

D 5

💡 Hint

The FREQUENCY function returns one more element than the number of bins.

The FREQUENCY function returns an array with length equal to the number of bins plus one. The extra element counts values above the highest bin. Here bins are 4, so output length is 5.

🎯 Scenario

advanced

Identify the error in histogram frequency calculation

You entered the formula =FREQUENCY(A2:A20, B2:B10) in cell C2 and pressed Enter. The output shows a single number instead of an array of counts. What is the most likely reason?

A You forgot to press Ctrl+Shift+Enter to enter it as an array formula.

B The data range A2:A20 is empty.

C The bins range B2:B10 contains text values.

D The formula syntax is incorrect; FREQUENCY requires three arguments.

💡 Hint

Think about how array formulas behave in older Excel versions.

In Excel versions before dynamic arrays, FREQUENCY must be entered as an array formula with Ctrl+Shift+Enter to output multiple values. Pressing Enter alone returns only the first element.

📊 Formula Result

expert

Determine the frequency output for mixed data and bins

Given the data in A2:A8: {2, 5, 7, 10, 12, 15, 20} and bins in B2:B4: {5, 10, 15}, what is the output array of =FREQUENCY(A2:A8, B2:B4)?

Excel

Data: 2, 5, 7, 10, 12, 15, 20
Bins: 5, 10, 15

A {2, 3, 1, 1}

B {2, 2, 2, 1}

C {1, 2, 2, 2}

D {3, 2, 1, 1}

💡 Hint

Count how many data points fall into each bin range: <=5, >5 and <=10, >10 and <=15, >15.

Bins: 5, 10, 15 create ranges: - <=5: values 2,5 → 2 - >5 and <=10: values 7,10 → 2 - >10 and <=15: values 12,15 → 2 - >15: value 20 → 1 So output is {2, 2, 2, 1}.