Frequency Table Conversion

Frequency Table to Individual Samples

We first show how to change data that is presented in frequency table format into individual samples with repetitions (i.e. raw data with repetitions).

Example 1: Convert the data in the frequency table with seven observed scores shown in range A5:B12 of Figure 1 into raw data format. The cumulative frequencies are shown in column C, although these won’t be used to create the raw data.

To accomplish this, we build the table on the right side of Figure B3.6. Column E contains the resulting 15 raw data elements in the sample and Columns F and G are used to create the data in column E.

Figure 1 – Conversion from frequency table to raw data

The Row column contains a pointer to the row in the Frequency Table that is being processed (the current row), starting with 1 and ending with 7 (since there are 7 rows in the Frequency Table). The Count column contains the number of times the score in the current row of the Frequency Table still needs to be duplicated.

Creating the Raw Data Table

To create the Raw Data table, we start by creating the first two rows of the table. The contents of the cells in these rows are as shown in Figure 2.

Figure 2 – Key Formulas

You can now fill in the values for the other rows in the Raw Data Table by highlighting the range E7:G20 and pressing Ctrl-D. This copies the formulas in the second row of the table into the other 13 rows of the table. Note that if you highlight a longer range and press Crtl-D you will get the same result for the first 15 rows of the table and then error values (which can then be removed).

Worksheet Functions

Real Statistics Functions: The following array function furnished by the Real Statistics Resource Pack provides similar functionality to that described above.

FREQ2RAW(R1, R2): outputs a column vector with the raw data corresponding to the data in the frequency table described by R1 and R2, where R1 contains the data items in the frequency table and R2 contains the  corresponding frequencies

FREQ2RAW(R1): equivalent to FREQ2RAW(R2,R3) where R2 is the first column of R1 and R3 is the second column of R1.

See Descriptive Statistics for Frequency Tables for examples of the use of the FREQ2RAW function.

Individual Samples to Frequency Table

Converting from raw data with possible repetitions to a frequency table is relatively simple. First, copy the data scores (using Paste Values if necessary) to a new place in the worksheet. Then select this range of cells and click on Data > Data Tools|Remove Duplicates. (If the data was not in sorted order, you would first use Data > Sort & Filter|Sort). Then use the COUNTIF function to count how many times each score appears in the sample data.

Example 2: Create a frequency table for the data in range A6:A20 of Figure 3. 

Figure 3 – Converting raw data to frequency table

The resulting frequency table is shown on the right of the Figure 3 using the procedure described above. E.g. cell D6 contains the formula =COUNTIF($A$6:$A$20,C6). The result is as Figure 3. Cell E7 contains the formula =E6+F7.

Also, see Example 2 in Discrete Probability Distributions for an example using the Real Statistics function FREQTABLE.

Data Analysis Tools

You can also use the Real Statistics Histogram with Normal Curve Overlay data analysis tool to create a frequency table from raw data. Use of this tool is explained in Discrete Probability Distribution. The output when leaving the Bin Size field blank is shown in the left most side of Figure 4 (omitting the histogram).

Figure 4 – Data analysis tool results

You can also use the same tool to create the output shown in the middle of Figure 4. This time the Bin Size is set to .1. See Histograms.

Finally, we can use Excel’s Histogram data analysis tool to obtain the result shown on the right side of Figure 4. This time, we must explicitly define the bins (as shown in range C4:C10). This is also explained in Histograms.

Examples Workbook

Click here to download the Excel workbook with the examples described on this webpage.

References

Turney, S. (2023) Frequency distributions: tables, types and examples
https://www.scribbr.com/statistics/frequency-distributions/

Langmann, K. (2024) How to make a frequency table in Excel
https://spreadsheeto.com/frequency-table-excel

Kaminski, J. (2024) Frequency table in math – definition & examples
https://brighterly.com/math/frequency-table/