**Frequency Distribution**

A Frequency Distribution is a summary of how often each value occurs by grouping values together.

*For example, you have data for class sections with the number of students in each section.*

Excel : Frequency Distribution |

- With
**COUNTIFS**Function - With
**FREQUENCY**Function

**Calculate Frequency Distribution in Excel**

1. Enter the above data in cells

**B3:C15.**The first row of table has headers.

The same data entered into a sheet in excel appears as follows :

2. Select the desired class intervals

3. Create a table with the columns - Class intervals, Lower limit, Upper limit and Frequency.

**How to calculate lower and upper limits using excel formula -**Suppose class interval column starts from cell E5 (excluding header).

**Lower limit -**

Enter the following formula in cell F5 and paste it down till the last row of the table.

=MID(E5,1,FIND("-",E5,1)-1)

**Upper limit -**

**Enter the following formula in cell G5 and paste it down till the last row of the table.**

=MID(E5,FIND("-",E5,1)+1,2)

**Frequency Distribution with COUNTIFS Function -**

To calculate the last column of the above table, enter the following formula in cell H5 and paste it down till the last row of the table

=COUNTIFS($C$4:$C$15,">="&F5,$C$4:$C$15,"<="&G5)

**Frequency Distribution with FREQUENCY Function**

Create a table with the columns - Class Intervals, Upper Limit and Number of Sections

Upper Limit can be calculated with the formula below in cell F5 -

--MID(E5,FIND("-",E5,1)+1,2)

**Paste the formula down till cell F9. Make sure you enter double minus (--) before MID function.**In the next step, select range

**G5:G9**and then enter

**FREQUENCY**Function

=FREQUENCY(C4:C15, F5:F9)

*F5:F9 refers to bins of Upper Limit.**Press*

**CTRL SHIFT ENTER**to submit the above**FREQUENCY**formula as it's an array formula. If it is entered correctly, you would see formula wrapped in curly braces { }**Histogram**

We can prepare histogram using frequency table.

**Steps:**

1. Highlight the values in

**class intervals**column**(Column E)**and**Frequency**column (**column H)**of the frequency distribution table.*(Holding down Ctrl key while highlighting two ranges)*
2. Click on the

**Insert**tab and select**2-D Clustered Column**.
3. Remove

**Series1 -**Select Series1 and Press Delete**Adding axis title**

1. Click on the chart.

2. Click the

**Layout**tab under**Chart Tools**.
3. Click

**Axis Titles**in the**Labels**group.
4. Select

**Primary Horizontal Axis Title**and then select**Title Below Axis**.
5. Select

**Primary Vertical Axis Title**and then select**Title Below Axis**.**Changing the axis scale**

2. Click the

**Layout**tab under**Chart Tools**.
3. On the

**Layout**tab of the ribbon, click on the**Axes**button.
4. Select

**Primary Vertical Axis**>> Select**More Vertical Axis**Options.
5. In the

6. For

7. Press

**Axis Options**section, for**Minimum**, select**Fixed**and enter the lowest number you want on your Y-axis. In this chart, i used**0.**6. For

**Maximum**, select**Fixed**, and type the number where you want the Y-axis to end. In this chart, i used**5**.7. Press

**Close**
...or you can just use the array formula frequency

ReplyDeletenicely DONE !!!

ReplyDeleteThank you so much for sharing this worth able content with us. The concept taken here will be useful for my future programs and i will surely implement them in my study. Keep blogging article like this.

ReplyDeleteMs excel training in chennai