bmax) = an array function that produces the frequency table for the data in R1, assuming equally sized bins of size bsize where bmax is the maximum bin size value Real Statistics Function: The Real Statistics Resource Pack supplies the following array function to create a frequency tableįREQTABLE(R1, bsize. This function can also be used to create a frequency table with equally spaced bins. Real Statistics FunctionĪs described in Discrete Probability Distributions, the Real Statistics Resource Pack provides the FREQTABLE function. The final output cell (E8) contains the number of data elements in the input range whose value is greater than the value of the last bin (i.e. data elements whose value is > 20 and ≤ 40). Similarly, E5 contains the number of data elements in the input range with a value in the second bin (i.e. the number of data elements whose value is ≤ 20). Here E4 contains the number of data elements in the input range with the value in the first bin (i.e. Excel now inserts frequency values in the highlighted range E4:E8. Since this is an array formula, you must press Ctrl-Shft-Enter. a column range with one more cell than the number of bins) and enter the formula To produce the output, highlight the range E4:E8 (i.e. Example using FREQUENCY functionĮxample 3: Create a frequency table for the 22 data elements in the range A4:B14 of Figure 5 based on the bin array D4:D7 (the text “over 20” in cell D8 is not part of the bin array).įigure 5 – Example of the FREQUENCY function The FREQUENCY function simply returns an array consisting of the number of data elements in each of the bins.

In this case, the midpoint of each interval is assigned the value x i.Įxample 2: Calculate the mean and variance for the data in the frequency table in Figure 4.įigure 4 – Calculations for a frequency table with intervals Often frequency tables are used with a range of data values, i.e. since there are 8 elements in the data set in Figure 2, we see that the frequency function for the random variable x is as shown in Figure 3 where each frequency value is divided by 8:įigure 3 – Frequency function corresponding to frequency table Example based on intervals Note too that a frequency table is closely linked to a frequency function, as defined in Definition 1 of Discrete Distributions. The results are the same as calculating the mean and variance by applying the formulas AVERAGE(A4:A11) and VAR.S(A4:A11), with reference to the cells in Figure 1. Here cell F11 contains the formula =D11/E11, which calculates the mean, and cell G14 contains the formula =(D14-E14*F14)/(E14-1), which calculates the variance. The necessary calculations are displayed in Figure 2. Simple ExampleĮxample 1: Calculate the mean and variance of the sample data from the frequency table in Figure 1.įigure 2 – Calculation of mean and variance from frequency table See Descriptive Statistics for Frequency Tables for information about calculating additional descriptive statistics for the data represented by a frequency table. We demonstrate this in the following example. Using these formulas we can calculate the mean and variance of sample data expressed in the form of a frequency table. Which can be calculated in Excel by the formula Using Property 1 of Measures of Variability, we also have the following alternative approach to calculating the variance: Where R1 and R2 are as above and R3 is a cell that contains the sample mean (as described above). In a similar way, the sample variance can be calculated as When data is provided in the form of a frequency table, the calculation of the mean and standard deviation cannot be performed directly using the usual AVERAGE and STDEV Excel functions. The table in Figure 1 shows that the data element 2 occurs 4 times, the element 4 occurs 2 times and the elements 3 and 5 occur 1 time. For example, the data in range A4:A11 of Figure 1 can be expressed by the frequency table in range C4:D7. Often data is presented in the form of a frequency table.

How to find median on numbers for mac