Frequency Distribution Formulas in LibreOffice Calc

LibreOffice Calc provides several formulas and tools to calculate frequency distributions, allowing users to organize raw datasets into structured frequency tables, intervals, and relative proportions. This guide details the essential Calc functions used to determine absolute frequencies, calculate relative and cumulative distributions, and establish optimal class intervals for statistical analysis.

The FREQUENCY Function

The FREQUENCY function is the primary method for calculating absolute frequency distribution across specified intervals (bins) in LibreOffice Calc.

• Syntax: =FREQUENCY(Data, Classes)
• Parameters:
  • Data: The reference range containing the values to be counted.
  • Classes: The reference range containing the upper limits of the bin intervals.
• Usage: Because FREQUENCY is an array formula, select the output range adjacent to your bins, enter the formula, and press Ctrl + Shift + Enter. Calc calculates how many data values fall into each interval, including values less than or equal to the first bin and greater than the final bin.

Interval Counting with COUNTIF and COUNTIFS

For discrete categories or customized bin intervals without using array formulas, conditional counting functions can be used:

• Single-Value/Categorical Frequency:
  • Formula: =COUNTIF(Data_Range, Category_Cell)
  • Counts the exact occurrences of specific labels, text, or individual numbers.
• Range-Based (Bin) Frequency:
  • Formula: =COUNTIFS(Data_Range, ">=" & Lower_Bound, Data_Range, "<=" & Upper_Bound)
  • Counts values falling strictly between a lower limit and an upper limit.

Relative and Percentage Frequency Calculations

Relative frequency indicates the proportion of total observations belonging to each class.

• Relative Frequency:
  • Formula: =Frequency_Cell / COUNT(Data_Range) or =Frequency_Cell / SUM(Total_Frequency_Range)
• Percentage Frequency:
  • Formula: =(Frequency_Cell / COUNT(Data_Range)) * 100 (or format the relative frequency cell as a percentage).

Cumulative Frequency Calculations

Cumulative frequency tracks the running total of frequencies across consecutive bins.

• Cumulative Absolute Frequency:
  • For the first bin: Reference the first frequency cell directly (=B2).
  • For subsequent bins: Add the previous cumulative total to the current bin frequency (=C2 + B3) or use an expanding range sum (=SUM($B$2:B3)).
• Cumulative Relative Frequency:
  • Formula: =Cumulative_Frequency_Cell / COUNT(Data_Range)

Supporting Formulas for Constructing Frequency Bins

To set up an accurate frequency distribution table from continuous data, Calc formulas help determine interval sizes and bin limits:

• Total Count (\(N\)): =COUNT(Data_Range)
• Data Range (\(R\)): =MAX(Data_Range) - MIN(Data_Range)
• Number of Classes (\(k\) via Sturges’ Rule): =1 + 3.322 * LOG10(COUNT(Data_Range))
• Class Width (\(w\)): =ROUNDUP((MAX(Data_Range) - MIN(Data_Range)) / Number_of_Classes, Decimals)