Using Excel in the ways that will be useful in the Project
GOALS:
Copy data to new sheet
Sort data (in several ways)
(is useful for finding subgroups data)
Descriptive Statistics:
find mean, standard deviation, five number summary
(find these for entire data set, and also for subgroups)
Graphs:
Pie Chart & Bar Chart – of Gender data
Double Bar Histogram of Height
On wamap, in the block for “Calculator, Excel, and other Technology Info & Instruction”, in the Excel Instructions block, there is an item for “Practicing Excel for the Project”. It contains an Excel file with data in it that we can use for practice. It also contains these instructions.
Also, the Excel Instructions block has links to videos of Excel functions.
STEPS TO TAKE
- Copy worksheet to another worksheet (so that original sheet is still existing in case you need to go back to it). Should save a separate file of the raw data values, for the actual project.
-Right Click on name of worksheet, select “move or copy sheet”, then select “(move to end)” and ALSO click in box next to “create a copy”. Then “ok”.
-The new sheet has its own tab. Double click on that tab and give it a good name. In our example, “Gender analysis”
- SORT
-Note: We want to sort one column into order, but we want all the data in the same row to stay together in the same row. So we must highlight ALL the columns that have data. So, highlight the columns of data
-Then click on “Data” tab along the top of the ribbon, then click on “Sort” in the ribbon (fairly near the middle). A dialog box opens.
-Next to “Sort by” click on the pull-down arrow to select which column to sort on. In this case, we’ll choose Gender.
-Then click on the “Add level” button so we can add a second column to sort on after the first one. And in the “Then by” box use the pull-down arrow to select height.
-Note that you can choose the “order” to sort (A to Z or else Z to A, or small to large or large to small). We’ll just use the usual order of A to Z and Small to large.
-Press okay. The data will be sorted. Note that the rows of data all stayed together in the same rows. Females are first (since that is alphabetically before males). Within Females, the heights are in order from small to large.
- Mean, Standard Deviation, Five-Number-Summary
-Excel functions typically have this format: begin with an equal sign, then the function’s name, then open parentheses, then whatever inputs are needed, then close parentheses.
- The mean =average( first cell : final cell of the range)
-Standard Deviation of a sample=STDEV.S(first cell : final cell of range)
-Median =median( first cell: last cell of range)
-Minimum =min( first cell: last cell of range)
-Maximum = max( first cell: last cell of range)
-First quartile =quartile( first cell : last cell of range , 1)
-Third quartile =quartile( first cell : last cell of range , 3)
continued
- Pie Chart – for Gender data
-We want the number of females and of males. This can be found simply by seeing the first and last cells that are labeled female (and then male) and subtracting.
-Type the names for the pie cart wedges in cells in a column (in this case, female and male). Next to each wedge name, type the number of items in that category. In this case 150 and 86.
-Highlight the wedge-names and the numbers-in-the-wedge and click Insert tab at the top, then Chart, then the pie-chart.
-The chart can be manipulated to get a title (that is important to have!), and to display numbers and percents in the wedges, etc.
-To add some text to the chart: Click in the chart, then in the “Chart Tools” display click on the Layout tab. In the Insert group, click Text Box. Then in the chart click where you want to start the corner of the text box and drag until the text box is the size you want. Then type and it will appear in the text box. When finished, click outside the text box.
-A Bar Chart of this same data could have been made instead of a Pie Chart, in a similar way.
- Double-Bar Histogram of Height data for Females and Males
-First we’ll copy our sorted data into a new worksheet. Create a new worksheet by double clicking on the tab in the bottom row next to the previous worksheet. In the worksheet with the sorted data, highlight the four columns of data, and press control-C for copy or else right-click and choose copy. Then go to the new worksheet and click in the upper left cell. Then press control-V for paste or else right-click and choose to paste.
-We need to find the min and max of all the height data (59.5 and 76.5) and decide on the classes. The data range is 17 inches, so let’s use 3-inch class size starting at 59.
So 59-61.9, 62-64.9, 65-67.9, etc.
-Set up columns with the lower and upper limits of the classes. Label a column for female and one for male, for putting the frequencies below those labels.
-To get frequencies:
[We need to go through this process twice – once for females and once for males.]
Highlight the cells that will contain the frequencies. While they are highlighted, start typing =frequency( and inside the parentheses put the data array and then the “bin array”. The bin array is the cells with the upper limits of the classes. Then end the parentheses. Do NOT press enter! Rather, hold down the Ctrl and Shift keys while you press Enter.
-Highlight the 2 columns of frequencies and their labels (female, male). Click Insert, then Chart, and then select column chart.
-Select the format for the chart giving a title and axis labels. Type them in.
-To get the horizontal axis labeled correctly, first have a column with the labels you’d like to have on the horizontal axis. Then click on the data on that axis to select it, right-click, in that box choose “Select data”. Under the right-side “Horizontal (Category) Axis Labels, click on the “Edit” button. Then highlight the cells containing the info you’d like to have on the horizontal axis. [That might be the lower limits. Or it might be the whole class labels.]
-If the chart no longer has the “legend” to show which bar colors mean what, to get it back: Click inside the chart. Under “Chart Tools” at the top, select Layout. In the Labels section, select Legend, and then select the option you prefer.
- Pie Chart for “Cheating”
What the data means:
Would you tell the instructor if you saw somebody cheating on an exam? (0=No, 1=Yes)
-To see how many answer “0” we can use the countif function:
=countif(data-array,condition) is the format
=countif(d2:d151,0) will count the number of cells with 0 in the data range
=countif(d2:d151,1) will count the number of cells with 1 in the data range
=countif(a2:a237,”Female”) will count the number of cells with Female in the data range