The final output cell (E8) contains the number of data elements in the input range with value > the value of the final bin (i.e. data elements whose value is > 20 and ≤ 40). Similarly, E5 contains the number of data elements in the input range with value in the second bin (i.e. Here E4 contains the number of data elements in the input range with 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. The FREQUENCY function simply returns an array consisting of the number of data elements in each of the bins.Į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 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 in Figure 3 where each frequency value is divided by 8:įigure 3 – Frequency function corresponding to frequency table Note too that a frequency table is closely linked to a frequency function, as defined in Definition 1 of Discrete Distributions. 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 element 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 kommentar(er)
