# How to use Excel's IF function

00:00:05,872 --> 00:00:42,085
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.

00:00:43,079 --> 00:01:17,787
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.

00:01:17,787 --> 00:02:06,909
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.

00:02:06,909 --> 00:03:09,106
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.

00:03:09,106 --> 00:03:50,331
This should create our frequency table like this. We want to express these numbers aspercentages 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.

00:03:53,053 --> 00:04:13,021
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