We're going to make a frequency table using data from one country.
In previous walkthroughs we filtered the data and copied it to a new sheet. But if you don't want to do this,
or you can't, you can use Excel's IF function.
To count the observations for a specific country only
we will need to use the IF function and FREQUENCY function together. The IF function is
very versatile, so it's good to know how to use it. The first thing we're going to do is
make a new sheet
and add the intervals in the first column.
We're picking out US data,
but you can use any country of course. The logic is the same. Remember if you add the first two numbers here -
they are 1 and 1.2 - Excel will understand the pattern when you copy down.
In this case we're copying down until 5.
Add two other headers: 'Frequency' and 'Proportion of firms'. We're going to
use these headers later. Select all the cells in column B that will hold the frequency data.
Type =FREQUENCY and open a bracket as before but now type IF and open another bracket, like this.
IF has three arguments: the test, the value it returns if that test is true, and the value it returns if that test is false.
Our test is whether the data is from the US. If that is the case,
then we want it to return the data in column A, and if it's not true, we want it to return nothing.
We need a source of data for the argument so go back to the data sheet and click on the top
of column N to select it. That's the column with the country names.
Type =“United States“. Note that because the value we're testing is text, it must have
quotes around it and be careful when you type it so it matches the country name in a data exactly.
Then type a comma and select column A. This is the data it will send to the frequency function.
Then another comma and type double quotes (“) twice with no space in between. This is what's called a null value,
meaning that if the IF test fails, it returns no data. Close the bracket but don't hit Enter yet,
we need to complete the frequency function now. Add another comma, click back to the frequency table tab,
and select the cells containing the interval values - those are the ones in column A.
Close the bracket and type Control+Shift+Enter together to fill in all the cells in column B at the same time.
This should create our frequency table like this.
We want to express these numbers as percentages in the third column,
and so click on the first cell in column C,
type the formula for a percentage which is B2 divided by the sum of B2 to B22 times 100 and hit Enter.
Now note we used dollar signs for the sum which means that the column of numbers added up never changes
and we need to do that because then we drag the box down to fill in the percentages.
One final thing having: a lot of decimal places in data like this is not a good idea it makes the data hard
to understand and that amount of precision is misleading.
So set the cell contents to be a number and use the rounding tool to express the data to one or maximum two decimal places