Read Lecture 3. React to the material in this lecture. Is there anything you found to be unclear about setting up and using Excel for these statistical techniques? Looking at the data, develop a correlation table and regression (either linear or multiple) for a variable—other than compa-ratio or salary—you feel might be important in answering our equal pay for equal work question. Interpret your results. 

BUS 308 Week 4 Lecture 3

Developing Relationships in Excel

Expected Outcomes

After reading this lecture, the student should be able to:

1. Calculate the t-value for a correlation coefficient
2. Calculate the minimum statistically significant correlation coefficient value.
3. Set-up and interpret a Linear Regression in Excel
4. Set-up and interpret a Multiple Regression in Excel

Overview

Setting up correlations and regressions in Excel is fairly straightforward and follows the
approaches we have seen with our previous tools. This involves setting up the data input table,
selecting the tools, and inputting information into the appropriate parts of the input window.

Correlations

Question 1

Data set-up for a correlation is perhaps the simplest of any we have seen. It involves
simply copying and pasting the variables from the Data tab to the Week 4 worksheet. Again,
paste them to the right of the question area. The screenshot below has the data for both the
question 1 correlation and the question 2 multiple regression pasted them starting at column V.
You can paste all the data at once or add the multiple regression variables later (as long as you
do not sort the original data).

Specifically, for Question 1, copy the salary data to column V (for example). Then copy
the Midpoint thru Service columns and paste them next to salary. Finally copy the Raise column
and paste it next to the service column. Notice that our data input range for this question now
includes Salary in Column V and the other interval level variables found in Columns W thru AA.

Question 1 asks for the correlation among the interval/ratio level variables with salary
and says to exclude compa-ratio. For our example, we will correlation compa-ratio with the
other interval/ratio level variables with the exclusion of salary. Since compa-ratio equals the
salary divided by the midpoint, it does not seem reasonable to use salary in predicting compa-
ratio or compa-ratio in predicting salary.

Pearson correlations can be performed in two ways within Excel. If we have a single pair
of variables we are interested in, for example compa-ratio and performance rating, we could use
the fx (or Formulas) function CORREL(array1, array2) (note array means the same as range) to
give us the correlation.

However, if we have several variables we want to correlate at the same time, it is more
effective to use the Correlation function found in the Analysis ToolPak in the Data Analysis tab.
Set up of the input data for Correlation is simple. Just ensure that all of the variables to be
correlated are listed together, and only include interval or ratio level data. For our data set, this
would mean we cannot include gender or degree; even though they look like numerical data the 0
and 1 are merely labels as far as correlation is concerned.

In the Correlation data input box shown below, list the entire data range, indicate if your
data has labels or not (good idea to include these), select the output cell, and click OK. Here is a
screen shot of the input box and some of the data.

The result will show up in K08 (in this case).

Statistical Significance

Part b. Normally, we would go thru our questions about the p-value for each value. But
since you are familiar with the testing logic, for this question we are going to “shortcut” the
process. Now, there is an easier way of determining which of the correlations are statistically
significant. This is suggested by the question1 part b that we skipped in lecture 2. We noted that
values smaller than the r = .20 that we tested could be assumed to all be non-significant. We
could also have assumed that values larger than the tested 0.50 would be assumed to all be
significant. So, it would seem to make sense that there is a specific value of r that exactly
matches the alpha = 0.05 criteria.

If we can find this value of r, we can compare each correlation with this critical value;
correlations larger (absolute values) than this are significant; while smaller correlations are not
significant. Having this critical value would give us a quick decision point (much like how we
use the p-value).

The issue is now, what is this critical r value?

Technical Point. If you are interested in how we obtain the formula for determining the
minimum r value, the approach is shown below. If you are not interested in the math, you can
safely skip this paragraph, and go to The Result paragraph below.

We know that t = r* sqrt(n-2)/sqrt(1-r2)

Multiplying both sides by sqrt(1-r2) gives us t *sqrt (1- r2) = r*sqrt(n-2)

Squaring both sides gives us: t2 * (1- r2) = r2* (n-2)

Multiplying each side out gives us: t2– t2* r2 = n r2-2* r2

Adding t2* r2 both sides gives us: t2= n* r2-2*r2+ t2 *r2

Factoring gives us: t2= r2 *(n -2+ t2)

Dividing both sides by *(n -2+ t2) gives us: t2 / (n -2+ t2) = r2

Taking the square root gives us: r = sqrt (t2 / (n -2+ t2)

The Result. The formula to use in finding the minimum correlation value that is
statistically significant is: r = sqrt(t^2/(t^2 + n-2)), where t is the 2-tail value for any df count.

We would find the t value associated with a two-tail p-value of 0.05 and a df value of 48
by using the t.inv.2T(alpha, df) function with alpha = 0.05 and df = n-2 or 48 (for our data set of
50 employees). Plugging these values into the gives us a t-value of 2.0106 or 2.011(rounded).

t =t.inv.2T(alpha, df) =t.inv.2t(0.05,48) = 2.011

r = sqrt(t^2/(t^2 + n-2)) = sqrt(2.011^2/(2.011^2 + 50-2) = 0.278.

Therefore, in a correlation table based on 50 pairs, any correlation greater than or equal to
0.278 would be statistically significant.

So, what does all this mean? If we find a correlation based on 50 pairs of data (such as
what our data set will produce), any correlation value that exceeds an absolute value of 0.278
would be found to be statistically significant (p-value less than 0.05) and cause us to reject the
related null hypothesis of not significant.

So, when looking at a table of correlation values, we can identify the significant
correlations immediately; these are any correlation above the absolute value of 0.278 (that means
larger than + 0.278 (such as + .46) or less than -0.278 (such as -0.53)). Knowing how to
interpret table results, we can proceed making our decisions on what is significant.

So, for part b, the first question asked is what is the T value that cuts off the two tails of
the distribution with an alpha of 0.05? We calculated this above as 2.011. The second question
asks for the associated correlation value for this t-value. Again, we found this above to be 0.278.

Spearman’s. Note that while the Spearman’s rank order correlation is not asked for in
the assignment, you might want to use it at times. For example, some could argue that
Performance Rating, since it is based mostly on human judgement, it is really ordinal and
requires Spearman’s. The formula for Spearman’s (which needs to be manually input into
Excel), is:

Rho = 1 – 6*(Sum of d^2)/(n*(n^2 – 1)); where d is the difference in the rank score for
each of the paired variables, and n is the count of paired data used. Remember that the ^ in an
Excel formula means take the number to that power, so d^2 means d squared (or d times d).

Regression

Question 2

Both linear and multiple regression are both set up in the same fashion, so we will look at
only the multiple regression situation. For the data, put the dependent variable, the output such
as salary or compa-ratio, in one column and then paste the independent, input, variables in

sequential columns next to it. Make sure that none of the columns contain letter characters. It is
also a good idea to include the variable labels for each data column. The first screen shot above
shows the data input required for this question.

The Regression function is found in the Data | Analysis block and is labeled Regression.
Here is a screen shot of a complete Regression set-up for a regression equation for compa-ratio.
Note that unlike the correlation input, we have two ranges to work with. The first is the output,
which for this example is compa-ratio (and would be salary for the homework). The second is
for the inputs, which should include all of the numeric looking variables, including the Degree
and Gender variables as shown below.

Data range entry for the Y (or outcome) and the X (or input) variables are done separately
by either typing in the ranges or using dragging the cursor over the data range after clicking on
the up arrow at the right end of the data entry boxes. The same is done with the data entry box
after clicking the circle for Output range.

There are a number of options to consider. First, of course, is the need to click the labels
box if your data ranges include labels. A second option is the Constant is Zero equation. This
would force the regression equation to pass thru the X = 0 and Y = 0 origin, even if this is not the
best fit. Use this with caution, even though it might make sense to have Y = 0 when all the X
variables are 0, using this option may not give us the equation that best fits the data.

The residuals box provides a way to see how well each of the plotted data points fits with
the predicted results. This will often allow us to see outliers – cases that do not fit with the rest
of the data set. Outliers are sometimes indications of data entry errors or, in the case of salary,
they may be paid using a different approach. One such example would be a commission
salesperson being included with employees that are paid on a straight salary, the basis of pay is
so different these two should not be analyzed in the same study. Other options here allow for the
results to be turned into Z-scores (Standardized Residuals), plotted on a graph, or have linear
plots made for the output and each separate input. Normal Probability Plots are rather

complicated to discuss, and it is left to the student to explore this if desired. You are encouraged
to play around with some of these options, even though they are not required for the assignment.

Here is a video on Regression: https://screencast-o-matic.com/watch/cb6jfuIk8S

Summary

Pearson Correlations are fairly easy to produce in Excel with either the Analysis ToolPak
Correlation function (best used for multiple correlations or when you want the labels shown) or
the Fx (or Formulas) function CORREL, best used for a single correlation outcome with no
labels. Both are used for the Pearson correlation only. The Spearman’s correlation requires
setting up the data in rank order and providing ranks for each variable separately and then
summing these and placing them into a cell formula to obtain the correlation.

Setting up the data for a correlation is fairly simple. Just list the variables in a column and
select the appropriate columns for the function being used. For the correlation table, have all the
variable columns in a continuous range.

The statistical significance of either correlation is found using the t formula t = r* sqrt(n-
2)/sqrt(1-r2), where r is the correlation value and n is the number of data pairs used for the
correlation. Once we have a t-value we can use the t.dist.2t(t, df) formula (df -= n-2) to find the
two tail p-value. The lecture presents an approach for finding a minimum statistically significant
value when we have a table of correlations to look at; correlations with an absolute value equal
to or greater than this value would be statistically significant.

Data set-up for a regression is similar to the correlation table. Have the outcome variable
at one end of the range so it can be selected alone and have all the other input variables listed in a
continuous range.

The regression function (for either a linear or multiple regression) is located in the
Analysis ToolPak list and is called Regression. The set-up within the data entry box is similar to
the other functions we have done (t-test, ANOVA, etc.) with a data range, data output location,
and a label box to fill in.

Please ask your instructor if you have any questions about this material.

When you have finished with this lecture, please respond to Discussion Thread 3 for this
week with your initial response and responses to others over a couple of days.

https://screencast-o-matic.com/watch/cb6jfuIk8S