4.2 Calculating the frequency of a statistical series
04-Oct-2022
2 mins
The frequency of a data item within a statistical series corresponds to the number of times it appears there.
For example, the graph below shows the number of operational health centres by province:
There are several methods to obtain the frequency of a statistical series with Excel. The first option is to use the =COUNTIF() function. COUNTIF counts the number of cells that meet a criterion; for example, counts the number of provinces where there are no health centres. In the example below, the 1st formula counts the number of cells where the number of health centres is equal to 0. The 2nd formula counts the number of cells where the number of health centres is greater than 0.
An alternative to the =COUNTIF function is =FREQUENCY(). This function is quite simple to set up and allows statistics to be obtained in a way that is sometimes faster than when using COUNTIF.
A third way to obtain the frequency of a statistical series is by using PivotTables.
A PivotTable is a powerful tool for calculating, synthesizing, and analysing data, allowing you to see comparisons, patterns, and trends in your data. More information in section 4.4 PivotTable.
The following exercise is only available on the French page at this stage- translation coming soon!
In order to practice calculating the frequency of a statistical series, here is a practical exercise using clean data from the case study - developed by CartONG as part of the Data Analysis Toolbox publication. A more complete presentation of this case study can be found in the “Data Analysis Toolbox” – Part “7 The case study”.
In this example, we are looking at basic household needs. We suggest that you calculate the following frequencies:
- Affordable goods and services for the household
- The main source of water in the household.
Use the [“Excel Toolbox - Analysis 4.2 - Practice exercise - V1 - Statement”] file to practice calculating the frequency of a statistical series.
In case of difficulties, or to compare your results with those of the case study, refer to the file [“Excel Toolbox - Analysis 4.2 - Practical exercise - V1 – Solutions”].