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.