Histogram and frequency analysis in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to count how many times the value in cell A2 appears in the range B2:B10.

Excel

=COUNTIF(B2:B10, [1])

Drag options to blanks, or click blank then click option'

AA2

BB2

C"A2"

DB2:B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using the range B2:B10 as criteria instead of a single cell.

Putting the cell reference in quotes, which counts the text 'A2' literally.

The COUNTIF function counts how many cells in the range B2:B10 match the value in A2. So, the criteria should be the cell reference A2.

2fill in blank

medium

Complete the formula to create a frequency distribution using the FREQUENCY function for data in A2:A15 with bins in C2:C5.

Excel

=FREQUENCY(A2:A15, [1])

Drag options to blanks, or click blank then click option'

AC2:C5

BB2:B15

CA2:A15

DD2:D5

Attempts:

3 left

💡 Hint

Common Mistakes

Using the data range as bins.

Using a range that does not contain bin limits.

The FREQUENCY function needs the bins array as the second argument, which is the range C2:C5 here.

3fill in blank

hard

Fix the error in the formula to calculate the relative frequency of values in B2:B10 by dividing each count by the total count in B11.

Excel

=B2/[1]

Drag options to blanks, or click blank then click option'

ASUM(B2:B10)

BB11

CCOUNT(B2:B10)

DSUM(B2:B11)

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM(B2:B10) instead of the total cell B11.

Using COUNT which counts numbers, not the total frequency.

The total count is stored in cell B11, so dividing B2 by B11 gives the relative frequency.

4fill in blank

hard

Fill both blanks to create a formula that counts values in A2:A20 greater than 50 and less than or equal to 100.

Excel

=COUNTIFS(A2:A20, [1], A2:A20, [2])

Drag options to blanks, or click blank then click option'

A">50"

B"<=100"

C"<100"

D">=50"

Attempts:

3 left

💡 Hint

Common Mistakes

Using <100 instead of <=100 to include 100.

Not putting comparison operators in quotes.

COUNTIFS counts cells that meet all criteria. Here, the first criteria is values greater than 50, and the second is values less than or equal to 100.

5fill in blank

hard

Fill all three blanks to create a formula that calculates the percentage frequency of values in B2:B10 relative to the total in B11, formatted as a percentage.

Excel

=TEXT(B2/[1], [2])

Drag options to blanks, or click blank then click option'

ASUM(B2:B10)

BB11

C"0.00%"

D"#,##0"

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM(B2:B10) instead of the total cell B11.

Using a number format instead of a percentage format.

The formula divides the count in B2 by the total in B11 to get the fraction, then TEXT formats it as a percentage with two decimals.

Practice

1. What does the FREQUENCY function in Excel do when given a data array and bins array?

easy

A. Finds the maximum value in each bin

B. Sorts the data array in ascending order

C. Calculates the average of numbers in each bin

D. Counts how many data points fall into each bin range

Solution

1. Step 1: Understand FREQUENCY function purpose

   The FREQUENCY function counts how many values from the data array fall into each bin range defined by the bins array.

2. Step 2: Analyze the function output

   It returns an array where each element shows the count of data points within each bin interval.

3. Final Answer:

   Counts how many data points fall into each bin range -> Option D

4. Quick Check:

   FREQUENCY counts data per bin [OK]

Hint: FREQUENCY counts items per bin, not sorts or averages [OK]

Common Mistakes:

• Thinking FREQUENCY sorts data
• Assuming FREQUENCY calculates averages
• Confusing FREQUENCY with MAX or MIN functions

2. Which of the following is the correct way to enter the FREQUENCY function as an array formula in Excel for data in A2:A10 and bins in B2:B5?

easy

A. =FREQUENCY(A2:A10, B2:B5)

B. =FREQUENCY(A2:A10; B2:B5)

C. =FREQUENCY(A2:A10 B2:B5)

D. =FREQUENCY(A2:A10, B2:B5) entered normally

Solution

1. Step 1: Check FREQUENCY function syntax

   The correct syntax uses a comma to separate arguments: =FREQUENCY(data_array, bins_array).

2. Step 2: Understand array formula entry

   In modern Excel, pressing Enter is enough; older versions require Ctrl+Shift+Enter, but syntax remains the same.

3. Final Answer:

   =FREQUENCY(A2:A10, B2:B5) -> Option A

4. Quick Check:

   Correct syntax uses comma and proper ranges [OK]

Hint: Use commas between arguments; no semicolons in standard Excel [OK]

Common Mistakes:

• Using semicolons instead of commas
• Missing comma between arguments
• Entering formula without array selection in old Excel

3. Given data in cells A2:A7 as {2, 5, 7, 3, 8, 10} and bins in B2:B4 as {3, 6, 9}, what is the result of =FREQUENCY(A2:A7, B2:B4) entered as an array formula?

medium

A. {1, 3, 1, 1}

B. {1, 2, 2, 1}

C. {2, 1, 2, 1}

D. {3, 1, 1, 1}

Solution

1. Step 1: Identify bins and count data per bin

   Bins are 3, 6, 9. Count how many data points fall ≤3, >3 and ≤6, >6 and ≤9, and >9.

2. Step 2: Count data points in each bin

   Values: 2 (≤3), 3 (≤3), 5 (>3 ≤6), 7 (>6 ≤9), 8 (>6 ≤9), 10 (>9). Counts: ≤3 -> 2 values (2,3), >3 ≤6 -> 1 value (5), >6 ≤9 -> 2 values (7,8), >9 -> 1 value (10).

3. Final Answer:

   {2, 1, 2, 1} -> Option C

4. Quick Check:

   Count per bin matches {2,1,2,1} [OK]

Hint: Count data points in each bin range carefully [OK]

Common Mistakes:

• Miscounting values on bin edges
• Ignoring the last bin for values above highest bin
• Confusing bin intervals as inclusive/exclusive

4. You entered =FREQUENCY(A2:A10, B2:B5) but the result shows only one number instead of an array. What is the likely cause?

medium

A. You pressed Enter instead of Ctrl+Shift+Enter in an older Excel version

B. The bins array is empty

C. The data array contains text values

D. The formula syntax is incorrect

Solution

1. Step 1: Understand array formula behavior in older Excel

   In Excel versions before dynamic arrays, FREQUENCY must be entered as an array formula using Ctrl+Shift+Enter to return multiple values.

2. Step 2: Identify cause of single value output

   If only Enter is pressed, Excel returns only the first element of the frequency array, showing one number.

3. Final Answer:

   You pressed Enter instead of Ctrl+Shift+Enter in an older Excel version -> Option A

4. Quick Check:

   Old Excel needs Ctrl+Shift+Enter for array formulas [OK]

Hint: Use Ctrl+Shift+Enter for array formulas in old Excel [OK]

Common Mistakes:

• Not entering as array formula in old Excel
• Assuming single number means formula error
• Ignoring Excel version differences

5. You have sales data in A2:A20 and want to create a histogram with bins B2:B6. You want to show the frequency counts in C2:C7. Which formula should you enter in C2:C7 to get correct counts, and how should you enter it?

hard

A. Enter =COUNTIF(A2:A20, "<="&B2) in C2 and drag down

B. Select C2:C7, enter =FREQUENCY(A2:A20, B2:B6), then press Ctrl+Shift+Enter

C. Enter =FREQUENCY(A2:A20, B2:B6) in C2 and press Enter

D. Select C2:C6, enter =FREQUENCY(A2:A20, B2:B6), then press Ctrl+Shift+Enter

Solution

1. Step 1: Determine output size for FREQUENCY

   FREQUENCY returns an array with one more element than bins, so for 5 bins, output needs 6 cells (C2:C7).

2. Step 2: Enter FREQUENCY as array formula

   Select C2:C7, type =FREQUENCY(A2:A20, B2:B6), then press Ctrl+Shift+Enter (in older Excel) to fill all cells with counts.

3. Final Answer:

   Select C2:C7, enter =FREQUENCY(A2:A20, B2:B6), then press Ctrl+Shift+Enter -> Option B

4. Quick Check:

   Output range size = bins + 1, enter as array formula [OK]

Hint: Output range must be bins count + 1, enter as array formula [OK]

Common Mistakes:

• Selecting wrong output range size
• Not entering as array formula in old Excel
• Using COUNTIF which counts differently