Using frequency function to calculate fractional segments in Excel is better than if function

How to use Excel to calculate the distribution of the number of students in each grade? Many articles are recommended to use the COUNTIF function, but every statistic a fraction of a function to write a, very troublesome.

For example, to show the distribution of numbers within a c58:c62 within a c2:c56 of less than 60 points, 60 to 70, 70 to 80, 80 to 90, and 90 to 100, enter the following 5 formula:

1. Number of persons with less than 60 points in the input formula within the C58: =countif (c2:c56, "<60")

2. Number of persons entering formula statistics from 90 to 100 in C59: =countif (c2:c56, ">=90")

3. The number of people entering formula statistics between 80 and 90 in C60: =countif (c2:c56, ">=80")-countif (c2:c56, ">=90"),

4. In C61, enter formula statistics between 70 and 80: =countif (c2:c56, ">=70")-countif (c2:c56, ">=80"),

5. In C62, enter formula statistics between 60 and 70: =countif (C2:C56, ">=60")-countif (c2:c56, ">=70").

If you want to place between 0 and 10, 10 to 20, 20 to 30 ... So many fractions from 90 to 100 are counted, and 10 formulas are written.

In fact, Excel has provided us with a frequency analysis of the frequency array function, which allows us to use an array formula to easily calculate the number of each fraction of the distribution. For example, we want to count the distribution of numbers within each fraction of 0 to 100 in the C2:C56 region:

1. Enter in b58:b68:0, 9.9, 19.9 、...... 9.9, 99.9, 100.

2. Using the mouse to select the area C58 to C69, enter "=frequency (c2:c56,b58:b69)" in the formula bar.

3. Press the "Crtl+shift+enter" key combination to produce the array formula "={frequency (C2:C56,B58:B69)}", here to note that "{}" can not be manually typed, you must press the "Crtl+shift+enter" key combination from the system automatically generated. When finished, c58:c69 will show the distribution of the scores.