We are going to learn how to create an APA-style frequency table using JASP,
Microsoft Word, and Excel.
Typically you will use Microsoft Word for writing your
research paper and although Word has some tools for
building tables, the secret to creating a proper table in Microsoft Word is to use
Microsoft Excel. All of the techniques that I will show you will also work if
you are using some other writing and spreadsheet software such as OpenOffice
or Libre Office. I am using a fictitious data set MyClass.SAV. This is
actually an SPSS data set, but we can open it easily in JASP. I will be
focusing on the variable Quiz4. These are scores on a statistics quiz for 112
students. The possible range of scores is 0 to 10 points.
Sometimes it's good to start with the ending.
For instance, I want to begin by showing you the final
product of this lesson, so that you will know what we are trying to achieve.
We will create a finished, APA-style, frequency table, just like this.
Our frequency table will include the range of scores in descending order, simple
frequency, relative frequency, cumulative frequency, and percentile, as well as the
sample size, which will go in the table title. To do this, we are going to get a
frequency table from JASP that looks like this. Although it is similar, it does
not contain all of the columns that we need. We will have to compute the last
three columns in Excel. This JASP table is not so different from the same
frequency table that we would get from SPSS, which would look like this. Actually,
all of the data are the same, except JASP included some information on
missing data. Here is our workflow: we will calculate basic frequencies in JASP, we
will copy the results into Excel, where we will then create and format our
frequency table, then we will bring the finished table into a research paper in Word.
This is a realistic workflow that you might follow for a real-world
write-up.
Once you have JASP open, we will open our
data by going to our main menu icon -> Open -> Computer -> Desktop. Locate the my class SAV
data set and click Open. As I mentioned, this is an SPSS data set. JASP can open
an SPSS file directly, as well as CSV data sets, but not an Excel file. Those
have to be converted to a CSV, first. As you can see
JASP has read the file and is showing you the data. We are interested in the
variable Quiz4: scores for a statistics quiz on a scale of 0 to 10.
JASP has correctly interpreted the level of measurement for this variable
as scale; however, we will need to change this, as I will show you in a moment.
Frequency is a descriptive statistic, so we will be using this Descriptives menu.
Select the option for Descriptive Statistics. In the results pane we
already see the table where the descriptive statistics are going to
appear. Move Quiz 4 into the variables box.
JASP uses progressive disclosure in the results pane. As soon as we enter the
variable, we already get a table with some descriptive statistics. Of course, we
are interested in a frequency table, and here is an option to ask for that table.
Because of progressive disclosure, you would
expect to see the frequency table appear... but it does not. The problem - as you may
have already figured out - is that frequency tables are for nominal and
ordinal variables, but Quiz4 has been coded as a Scale variable. So what can we do?
First, let's take a look at our variable in the data set. Click on the
disclosure triangle in the left column to "show data". Quiz 4 is properly coded
as a scale variable. JASP is saying that it will only do a frequency table on
nominal and ordinal data. Well, that makes sense.
Typically, we do not want frequency tables for continuous variables without
categories: they're too big, they're cumbersome, and quite honestly they're
useless. On the other hand, in this case, we actually do want a frequency table
for what we have listed as a scale variable. Well, this is a pickle: how can
we get a frequency table for our scale data?
We lie.
JASP is saying "I won't give
you a table unless your data are nominal." ...which is really for our own benefit
So we say "alright then, these data are nominal" even though we know that they're
actually scale. And then JASP says,"Oh, well, in that case... here you go" and it gives us
the table. Remember: you were not created for the software, the software was
created for you. You tell the software what to do, not the other way around.
Hover your cursor over the scale icon and "click here to change column type."
Change it to Nominal. This change now allows us to do something else very
important that we could not do with our data set to Scale. Recall that frequency
tables display the scores in descending order. We need to ask for that, too. Hover
your cursor over the word "Quiz4" and "click here to change labels."
This is something else that we can only do with nominal and ordinal variables.
We see the current values and labels. They are in ascending order, with the smallest
values on top. Click the double arrows to "reverse order all of the labels".
This reorders the sequence as 10, 9, 8... etc. just as we wanted. And now, click on the
disclosure triangle in the right column to "hide data" and to reveal the frequency
table in the results pane. There it is! We have four columns, including frequency.
The sample size is down here: 112. Because we have no missing cases, the "percent" and
the "valid percent" columns are exactly the same. Looks like everything that we
need for our finished table, right? Not so fast...
Actually, there is only one usable column of data here. Look at the column headings.
We want simple frequency for our table, so we need this first column labeled
"frequency". We also want relative frequency, cumulative frequency, and
percentile... but these columns are for percent, valid percent, and cumulative
percent... and this is "cumulative percent" not "cumulative frequency". The only data
that are useful to us are the descending list of scores and the simple frequency.
Now there are a few other things to notice with this table.
Look at this list of quiz scores. Yes, they are in descending order, but what
else do you see? Several missing categories. We are missing a 4 and a 1.
So we have some work to do. One of the benefits of JASP is that JASP gives us nicely-formatted
tables and figures. Sometimes, you can copy these tables directly into
your publication or poster. In this case, however, we are going to copy this table
into Excel, and then create a new table using these data. Hover your cursor over
the titles for this table and you will see disclosure triangles appear. Click on
the triangle next to the table title and you can Copy, Copy LaTeX, or add a note.
We want to copy this table to our clipboard.
Now open a new Excel workbook. Paste the table into cell A1. Now we are ready
to do some editing and to create our frequency table. First, I can add column
headings where I will be creating my new table. Across the top row type "Score", "f"
(for simple frequency), we need "relative frequency", "cumulative frequency," and
"Percentile". Formatting note: use a lowercase "f" to stand for frequency.
You should set it in italics... or if you want this cool "f", type option-F on Mac or
alt-F on PC. Next, we should copy our scores into the score category, and we
can copy our frequency data and place it in the frequency column. Next, let's deal
with these missing categories. Highlight the entirety of Row 3 and then
right-click (or control-click) on the table. Choose "Insert" from the drop down
menu. This is our row for the score of 4. We also need a row for the score of 1.
Now that we have the rows for our missing categories, we need to add their
frequencies. The fact that they were missing in JASP means that they both
had a frequency of 0. So now we are ready to calculate relative frequency,
cumulative frequency, and percentiles. We will use Excel formulas to do the math
for us.
For instance, the relative frequency is the proportion of times that a score
occurs. Relative frequency is the simple frequency divided by the sample size.
In cell C17, type the formula "=B17/ 112
After you type an "=" and are creating the formula, you can simply click on
cells to reference them. Drag the formula down to copy it.
The cumulative frequency is the frequency of all the scores at and below
a particular score. The cumulative frequency of a score is the frequency of
that score plus the cumulative frequencies of all the scores below it.
To create this column, we need to start at the bottom. Type "2" for the first
cumulative frequency (same as the simple frequency)
above that, in cell D26, we want the previous cumulative frequency added to
the next simple frequency. Now drag that formula up the column. The top cumulative
frequency will be the same as the sample size.
Finally, percentile is the percent of all of the scores in the data that are at or
below a certain score. Percentile is the cumulative frequency divided by sample
size times 100. Start with the numbers in the CF column. Transform each cumulative
frequency value to a percentile by dividing the cumulative frequency by n (112),
then multiplying by 100. Double-click to copy that formula down and to
complete the column. Now we have all of the data in place. We can also do our APA
formatting right here in Excel. First, let's clear out everything that we do
not need. Drag to select the entire table and using the borders tool, select
"no border". If you had copied in a table from JASP that had shading, you would clear
any shading by selecting the fill color tool and choose "no fill". The font for
this table will be Times New Roman...12 pt.
Good, now we need to add rules horizontal - black lines - above and
below the table. Excel calls them "borders." Highlight the top row of data. Along our
five columns, using the borders tool, we will add a top rule and a bottom rule
on the top row. We will also add a bottom rule below the last row of data.
Rather than using the column spacing that Excel gives us, let's select the entire range
of columns and adjust their width slightly. They can be a little narrower.
I like to keep all of the columns the same width, when I can. Next, we should Center
the titles in the header row, and we should Center the scores in the first
column. Right justify all scores in other cells. This will help the decimal points
line up correctly... and speaking of decimals, we can use the formatting tools
to adjust the display of decimals. Let's select these two columns (which I am
selecting by holding down the command key on Mac) and use the decrease decimal
tool to make them all two decimals. You can also change these decimals one
column at a time. Of course, cumulative frequency does not need decimals, so we
can select only that column and set the decimals to zero. Okay, so we are done
formatting the APA-style table. It's time to get this table into Word and finish it.
Select the entire table, copy, and open a Word document. This is an example
document that I made up as if it were a class paper. Notice that I referenced the
table in the preceding paragraph... as you should.
Paste the formatted table here. Now we need to title the table. Each table must have
a number and a title. Numbers run sequentially. Your first table is titled
"Table 1" your second would be titled "Table 2" and so on. On the next line, add
the title: "Frequencies of Scores for Quiz 4." This is a good place to add the
sample size, as well (in parentheses). Table titles are always in title case and are italicized.
And there is our properly formatted, APA-style frequency table
created using JASP