QMB 3200 EXCEL FORMULAS – EXAM QUESTIONS AND
ANSWERS (WITH WORKED EXAMPLES)
Descriptive Statistics & Data Visualization
1. Frequency Formula
Formula: =COUNTIF(range, criteria)
Worked Example: You have cells A1:A10 containing the numbers: 5, 3, 5,
7, 5, 2, 8, 5, 1, 4. To count how many times the number 5 appears, you
type =COUNTIF(A1:A10, 5). The result is 4 because the number 5
appears four times.
2. Percent Frequency Formula
Formula: = (frequency cell) / SUM(range)
You need the frequency and the total frequencies.
Worked Example: Using the same data, the frequency of the number 5
is 4. The total count of all numbers is 10. The percent frequency is 4
divided by 10, which equals 0.4 or 40%. In Excel, you can type =4/10 or
=COUNTIF(A1:A10,5)/COUNT(A1:A10).
3. Relative Frequency Formula
, Formula: = (frequency cell) / (total cell)
You need the frequency and the total.
Worked Example: Frequency of the number 5 is 4, and the total number
of values is 10. The relative frequency is 4 divided by 10, which equals
0.4. In Excel, you type =4/10.
4. Histogram
Definition: A visual display of a frequency, relative frequency, or percent
frequency distribution. The variable of interest is on the horizontal axis,
and frequencies are on the vertical axis.
Worked Example: Using the data 5, 3, 5, 7, 5, 2, 8, 5, 1, 4, you can create
a histogram in Excel by selecting the data, going to Insert > Charts >
Histogram. The horizontal axis shows the possible values (1 through 8),
and the vertical axis shows how many times each value occurs.
5. Average (Mean) Formula
Formula: =AVERAGE(range)
Worked Example: For the numbers 5, 3, 5, 7, 5, 2, 8, 5, 1, 4 in cells
A1:A10, type =AVERAGE(A1:A10). The sum is 45, divided by 10 gives a
mean of 4.5.
6. Median Formula
Formula: =MEDIAN(range)
ANSWERS (WITH WORKED EXAMPLES)
Descriptive Statistics & Data Visualization
1. Frequency Formula
Formula: =COUNTIF(range, criteria)
Worked Example: You have cells A1:A10 containing the numbers: 5, 3, 5,
7, 5, 2, 8, 5, 1, 4. To count how many times the number 5 appears, you
type =COUNTIF(A1:A10, 5). The result is 4 because the number 5
appears four times.
2. Percent Frequency Formula
Formula: = (frequency cell) / SUM(range)
You need the frequency and the total frequencies.
Worked Example: Using the same data, the frequency of the number 5
is 4. The total count of all numbers is 10. The percent frequency is 4
divided by 10, which equals 0.4 or 40%. In Excel, you can type =4/10 or
=COUNTIF(A1:A10,5)/COUNT(A1:A10).
3. Relative Frequency Formula
, Formula: = (frequency cell) / (total cell)
You need the frequency and the total.
Worked Example: Frequency of the number 5 is 4, and the total number
of values is 10. The relative frequency is 4 divided by 10, which equals
0.4. In Excel, you type =4/10.
4. Histogram
Definition: A visual display of a frequency, relative frequency, or percent
frequency distribution. The variable of interest is on the horizontal axis,
and frequencies are on the vertical axis.
Worked Example: Using the data 5, 3, 5, 7, 5, 2, 8, 5, 1, 4, you can create
a histogram in Excel by selecting the data, going to Insert > Charts >
Histogram. The horizontal axis shows the possible values (1 through 8),
and the vertical axis shows how many times each value occurs.
5. Average (Mean) Formula
Formula: =AVERAGE(range)
Worked Example: For the numbers 5, 3, 5, 7, 5, 2, 8, 5, 1, 4 in cells
A1:A10, type =AVERAGE(A1:A10). The sum is 45, divided by 10 gives a
mean of 4.5.
6. Median Formula
Formula: =MEDIAN(range)
