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. Cell G14 contains the formula =(D14-E14*F14)/(E14-1), which calculates the variance. Here cell F11 contains the formula =D11/E11, which calculates the mean. 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įigure 2 displays the necessary calculations. 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.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |