Keyboarders Guide to Excel Lesson 2
Lesson 2
Learning more about your data
New commands: countif, sumif
Review
Use the range naming procedures from Lesson 1 to name the database and columns.
If you were successful, you know you have a database that has 4 columns and and 135 rows. The top row is for headings which are the names of your variables. The remaining 134 rows are the entries in the database.
You know the names of the variables.
Number is an arbitrary number and is not a variable of concern.
Gender, Age, and Grade are the variables that have been recorded for each individual.
You can get basic information using the Excel statistical functions for each of the variables. Minumum, Maximum, Average, etc.
New Material
Now you are going to learn more powerful functions that answer more complex questions. But before we look at a complex problem, how about a simple one. How many males and females are in the sample?
How would you do that? Sort the data and count? That is one way to do it, but Excel has conditional statistical functions. That is, it has functions that count and sum selectively depending on the criteria you set.
The Countif function will allow you to get the number of males and females very easily. Countif will count the number of cases that meet a criterion.
=countif(gender,1)
=countif(gender,2)
The countif function will return the number of males and females.
Punctuation is critical here. How many entries are under 30? How many individuals have a score above 50
=countif(age, “<30”)
=countif(grade,”>50”)
Problem: Change the 1 and 2 values in Gender to female and male, m,f or whatever.
The F5 function key is the GoTo key. When you hit it you are presented with the range names in the worksheet. Choose gender and you will find yourself in the gender column with the whole column selected. We want to do a search and replace in the selected column. The search and replace command is called when you Ctrl-h. You will be at a dialogue box and you type a 1 in the first line and what you want to replace it with in the second line. The keyboard commands for this procedure: Alt-n brings you to the first line to enter a 1 or a 2. Alt-e brings you to the second line to enter f or m. Alt-a makes all the replaces. Hit ESC twice to exit.
Sumif is very similar to Countif, excepts it sums the values rather than counting
=sumif(grade, “>50") =sumif(age, “<40”) These values do not have very much meaning in themselves, but Sumif and Countif can be combined to create an average. So you could get the average grade of those scoring over 50 or the average age of those below 40.
Aside: Averageif does not exist in Excel 03 but is available in 07
In the next section you will add very powerful extension to SumIf and CountIf.
The conditional stat functions take a third argument although, up to now, we have limited it to two. You can see below the third argument. =sumif(gender,1,grade) The first two arguments are gender and 1 which we have already used. The third argument, grade, indicates what you want to sum. So in this case we are getting the sum of grades for those whose Gender is 1. You can use countif to find the total of Gender 1 and Gender 2 and calculate the averages. Note for those using Voice Synthesizers: Punctuation is critical here. If you use numbers, you do not need quotes. Anything else the term after the column must have quotes. Also the Less-than symbol is used, as in less than 30, but in symbols. You can go to the Forums to ask questions and give suggestions.
