Comprehensive Study Notes on Frequency Distributions, Histograms, and Data Skewness in Excel

Excel Frequency Distributions & Bin Terminology

• Frequency Distribution Setup in Excel:

  • Access path: Data tab $\rightarrow$ Data Analysis $\rightarrow$ Histogram $\rightarrow$ OK.

  • Input Range: Select the raw data cells containing the dataset values (e.g., test scores).

  • Bin Range: Can be left blank/open, allowing Excel to automatically generate class intervals, or specified manually.

  • Output Range: Specify a cell destination on the active worksheet where the frequency table will be populated.

• Interpretation of Excel "Bin" Column:

  • In manual frequency distributions, intervals are expressed with lower and upper limits (e.g., $5\text{--}10$, $11\text{--}16$, $17\text{--}22$, $23\text{--}28$) alongside raw count frequencies (e.g., $2$, $3$).

  • In Excel histogram output, the Bin column explicitly represents the upper class limit of each class interval.

  • The More bin row in Excel captures all data points exceeding the highest explicitly defined upper class limit (e.g., all data values strictly greater than $87.25$).

Relative Frequency & Percentage Frequency Distributions

• Conceptual Definition:

  • A relative frequency distribution measures the frequency of an individual class in direct comparison to the total frequency of the entire dataset.

  • A percentage frequency distribution expresses these relative proportions as percentages.

• Mathematical Formulas:

  • Relative Frequency of a Class:     $\text{Relative Frequency} = \frac{\text{Frequency of a Class}}{\sum \text{Frequencies}}$

  • Percentage for a Class:     $\text{Percentage for a Class} = \left(\frac{\text{Frequency of a Class}}{\sum \text{Frequencies}}\right) \times 100\%$

• Excel Calculation & Cell Referencing Procedure:

  • Summing Frequencies:

  • Formula: =SUM(range) (e.g., =SUM(B2:B6)).

  • Function: Serves as a validation check. If the dataset contains $N = 20$ total observations, the sum of frequencies must equal $20$ to verify no data points were omitted.

  • Relative Frequency Formula & Absolute Referencing:

  • Basic relative frequency cell formula: =B2/20 or =B2/$B$7.

  • Using absolute referencing ($</code>): Placing dollar signs in front of the column letter and row number (e.g., <code>$B$7) locks the denominator cell reference.

  • Significance of Absolute Referencing: When dragging the formula fill handle down across subsequent rows, the numerator cell reference moves dynamically down the frequency column, while the locked denominator reference stays fixed on the total sum cell. Without absolute referencing, dragging down shifts the denominator cell downward into empty cells, resulting in a division by zero error (#DIV/0!).

  • Percentage Formula:

  • Formula: Select relative frequency cell and multiply by $100$ (e.g., =C2*100). Drag fill handle down to populate all rows.

• Analytical Purpose of Relative Frequency Distributions:

  • Primary Purpose: Comparing two or more datasets that differ in total sample size.

  • Comparative Example:

  • Class A ($N = 20$ total students):

    • Score $90\text{--}100$: $4$ students

    • Score $80\text{--}89$: $6$ students

    • Score $60\text{--}69$: $3$ students

  • Class B ($N = 40$ total students):

    • Score $90\text{--}100$: $8$ students

    • Score $80\text{--}89$: $12$ students

    • Score $60\text{--}69$: $4$ students

  • Analysis:

    • Comparing raw frequencies ($4$ vs. $8$ students scoring $90\text{--}100$) gives a misleading impression that Class B performed better.

    • Computing relative frequencies reveals that both classes had identical performance proportions:

    • $90\text{--}100$ Interval: Class A = $\frac{4}{20} = 20\%$, Class B = $\frac{8}{40} = 20\%$

    • $80\text{--}89$ Interval: Class A = $\frac{6}{20} = 30\%$, Class B = $\frac{12}{40} = 30\%$

    • Relative frequency removes sample size bias, enabling accurate normalized comparisons.

Cumulative Frequency Distributions

• Excel Generation Steps:

  • Access path: Data tab $\rightarrow$ Data Analysis $\rightarrow$ Histogram $\rightarrow$ Select Input Range $\rightarrow$ Select Output Range $\rightarrow$ Check the box for Cumulative Percentage $\rightarrow$ OK.

• Mathematical Structure & Relationship:

  • Cumulative percentage dynamically accumulates the percentages of current and preceding class intervals row-by-row.

  • Sequential Example:

  • Row 1 ($55\text{--}63$): Class Percentage = $5\%$, Cumulative Percentage = $5\%$

  • Row 2 ($64\text{--}72$): Class Percentage = $10\%$, Cumulative Percentage = $5\% + 10\% = 15\%$

  • Row 3 ($73\text{--}81$): Class Percentage = $20\%$, Cumulative Percentage = $15\% + 20\% = 35\%$

  • Row 4 ($82\text{--}90$): Class Percentage = $35\%$, Cumulative Percentage = $35\% + 35\% = 70\%$

  • Row 5 ($91\text{--}99$): Class Percentage = $30\%$, Cumulative Percentage = $70\% + 30\% = 100\%$

  • The final row of a cumulative percentage distribution must always equal $100\%$, as it represents the sum total of all class frequencies.

Hand Construction of Frequency & Relative Frequency Histograms

• Textbook Reference & Definition:

  • Covered under Section 2.2.

  • A histogram is a visual visual graph of a frequency distribution consisting of contiguous vertical bars drawn adjacent to one another without intervening gaps (unless a class interval has a frequency of zero).

• Step-by-Step Hand Construction Procedure:

  • Sample Frequency Distribution Data:

  • Interval $55\text{--}63$: Frequency = $2$

  • Interval $64\text{--}72$: Frequency = $4$

  • Interval $73\text{--}81$: Frequency = $4$

  • Interval $82\text{--}90$: Frequency = $6$

  • Interval $91\text{--}99$: Frequency = $4$

  • Step 1: Horizontal Axis (Scale) Setup:

  • Options for scale markers: Class Midpoints, Class Boundaries, or Class Limits.

  • Computing Class Midpoints:       $\text{Class Midpoint} = \frac{\text{Lower Class Limit} + \text{Upper Class Limit}}{2}$

  • Calculations for sample dataset:       $\text{Midpoint}_1 = \frac{55 + 63}{2} = 59$       $\text{Midpoint}_2 = \frac{64 + 72}{2} = 68$       $\text{Midpoint}_3 = \frac{73 + 81}{2} = 77$       $\text{Midpoint}_4 = \frac{82 + 90}{2} = 86$       $\text{Midpoint}_5 = \frac{91 + 99}{2} = 95$

  • Plot calculated midpoints ($59$, $68$, $77$, $86$, $95$) at equal spacing along the horizontal axis.

  • Step 2: Vertical Axis (Scale) Setup:

  • Label axis as Frequency.

  • Scale tick marks sequentially from $1$ to maximum frequency ($6$).

  • Step 3: Bar Construction Rules:

  • Rule 1: All bars must be of equal width.

  • Rule 2: Adjacent bars must touch each other continuously.

  • Heights drawn: Midpoint $59 \rightarrow 2$, Midpoint $68 \rightarrow 4$, Midpoint $77 \rightarrow 4$, Midpoint $86 \rightarrow 6$, Midpoint 95 \rightarrow 4$.\n\n- **Relative Frequency Histogram Construction**:\n - Total Frequency N = 2 + 4 + 4 + 6 + 4 = 20\n - Percentage Calculations per Class:\n    \text{Percentage}_1 = \left(\frac{2}{20}\right) \times 100\% = 10\%\n    \text{Percentage}_2 = \left(\frac{4}{20}\right) \times 100\% = 20\%\n    \text{Percentage}_3 = \left(\frac{4}{20}\right) \times 100\% = 20\%\n    \text{Percentage}_4 = \left(\frac{6}{20}\right) \times 100\% = 30\%\n    \text{Percentage}_5 = \left(\frac{4}{20}\right) \times 100\% = 20\%\n - Vertical Axis Scale: Percentage values (10\%$,$20\%$,$30\%).\n - Visual Property: The geometric shape of a relative frequency histogram is completely identical to the raw frequency histogram of the same dataset; only the vertical axis scale values change.\n\n# Generating & Formatting Histograms in Excel\n\n- **Generating Excel Histogram Charts**:\n - Dataset setup: Worksheets containing scores for 200 students.\n - Step 1: Open `Data` \rightarrow$`Data Analysis`$\rightarrow$`Histogram`$\rightarrow `OK`.\n - Step 2: `Input Range`: Select all 200 test score values (excluding Student IDs).\n - Step 3: `Bin Range`: Leave open/blank to allow Excel automated bin generation.\n - Step 4: `Output Range`: Select a blank cell location.\n - Step 5: Check `Chart Output` box (ensure `Cumulative Percentage` and `Pareto` options are unchecked) \rightarrow Click `OK`.\n\n- **Formatting Default Excel Output to Standard Histogram Rules**:\n - Eliminating Bar Gaps:\n - Right-click any vertical column bar \rightarrow Select `Format Data Series`.\n - Locate `Gap Width` setting \rightarrow$Set value to$0\%$$ (0). This forces all adjacent bars to touch continuously.

  • Aesthetic Formatting:

  • Fill Color: Select distinct fill color (e.g., orange).

  • Border/Outline: Select a contrasting dark border outline color to visually delineate adjacent bars clearly.

  • Axis & Title Relabeling:

  • Chart Title: Rename from default "Histogram" to descriptive title (e.g., "Histogram of Test Scores").

  • Y-Axis Label: Verify label is named "Frequency".

  • X-Axis Label: Rename default "Bin" label to "Class Intervals".

Shapes of Data Distributions & Skewness

• Normal Distribution (Bell-Shaped Distribution):

  • Exhibited in Dataset 1.

  • Defined by a symmetric, bell-shaped visual profile.

  • Seven Fundamental Characteristic Features:

  1. Overall Shape: Classic continuous bell shape.

  2. Symmetry: Symmetric around the central point.

  3. Center Concentration: The majority of observations are concentrated heavily in the central region.

  4. Tails Identification: Extreme left and right outer ends are designated as the distribution "tails".

  5. Tail Frequencies: Frequencies decrease systematically as distance from the center toward either tail increases.

  6. Bilateral Balance: Left side of the peak mirrors the right side.

  7. Continuous Tapering: Smooth decrease in counts extending symmetrically outward.

• Right-Skewed Distribution (Positively Skewed):

  • Exhibited in Dataset 3.

  • Data Concentration: The vast majority of data points are concentrated toward the left side (lower values) of the horizontal axis.

  • Tail Trait: Characterized by an elongated tail stretching toward the right (higher values).

  • Naming Convention: The direction of skewness is determined by the direction of the long tail, not the location of high data concentration. Because the long tail points right, it is classified as Right-Skewed.

• Left-Skewed Distribution (Negatively Skewed):

  • Exhibited in Dataset 4.

  • Data Concentration: The vast majority of data points are concentrated toward the right side (higher values) of the horizontal axis.

  • Tail Trait: Characterized by an elongated tail stretching toward the left (lower values).

  • Naming Convention: Classified as Left-Skewed because the long extended tail points to the left.