SWK 3401.4-2.2 Proportion and Percentage
Introduction
The purpose of this activity is to learn how to determine the proportion and percentage of times specific values of a categorical variable occur, using Excel. For example, in the coursebook, you read about men in a study who entered the treatment program voluntarily versus were involuntarily referred by the court or other legal proceedings. First, you will learn to compute the proportion, then you will learn to compute the percentages.
Instructions
- Open the file you saved from the last activity, or use the instructor’s file called leadlevels for frequency counts finish.xlsx. You might recognize this as being the complete file for the data you learned to enter earlier in the course—children from rental versus family owned homes (hypothetical).
- In the last activity, you counted how many were of each type in a categorical variable (type of home). This time, you will compute the proportion these counts represent. We already know some of the information we need to know: we know the count for each type (50 type 1, 128 type 2), and we know the total number of children (N=178). So, we could just do the calculation by hand and be done with it. But, what if the datafile is going to continue to grow—new study participants will be added. We don’t want to have to keep recalculating the counts and proportions by hand with each new addition. If we have a formula inserted in Excel, it will keep the calculations current.
- Click on an empty cell: maybe B182 is a good choice. Then, we just need to set up a simple division problem for Excel to compute. We want the answer from the countif command for rental home children (those of type 1 in column B) divided by the answer from the count command for the whole study (column A). Then, we do it again for the family-owned home children (those of type 2 in column B). It will look like this:
In cell B182, type in =B180/A180 to get the proportion of type 1 children.
In cell B183, type in =B181/A180 to get the proportion of type 2 children.
- Hopefully, you have the number 0.28 (rounding down) appear in cell B182 and the number 0.72 (rounding up) appear in cell B183. [Remember we round down if the digit is 0-4 and round up if the digit is 5-9.]You can confirm for yourself that the total of the proportion values is 1 by adding them together: (0.28+0.72=1.0).
- Now you can compute the percentage that each type represents. It is a simple multiplication problem: take each of the proportion values and multiply by 100%. The total of the two values should be 100%. Click on an empty cell, like C182. Then, tell Excel to compute this multiplication problem by inserting another formula:
In cell C182, type in =B182*100
In cell C183, type in =B183*100
- We can reformat the answer to get rid of the extra decimal places that we are not using.
Right click on cell C182. Choose format cell from the drop-down menu. Select “number” as the option and on the right side of the box, put in 2 for the decimal places to keep.
Right click on cell C183 and repeat these steps.
- If you got the answers 28.09 in cell C182 and 71.91 in cell C183, you succeeded in completing this learning activity! (The instructor version is in a file called leadlevels for proportion and percentages finish.xlsx.)
