How to Test the Significance of r in Excel: Examples

This video explains some of the concepts associated with t-tests. It focuses on how to do the calculations in Excel. The difference between Excel for Windows and Excel for Mac are very, very small when using the Data Analysis Toolpak.
If you have not installed the Data Analysis Toolpak (which comes free with Excel), the following video will show you how to do it.
Windows: • How to Install the Sta...
Mac: • Installing the Statist...

[@CodeWaka]

Thanks David your videos really helped me with this group project.

[@sevej.1318]

You are the absolute best, your explanation's are amazing!

[@flowermeadow]

This video was so helpful, thank you!

[@DavidDunaetz]

I'm glad you found it useful.

[@mofwzyy]

Thank you so much Dr. David for your helpful tutorial

[@TheZainjauda]

Thanks for this tutorial. May I request your advice please since I have little knowledge of statistics? I have a questionnaire of 25 questions (each with three options, agree, neutral, and disagree) for which I collected 221 responses. The respindents are of three linguistic backgrounds, Arabic, English, and a 3rd language. How can I check if there is a relationship between the respondents' linguistic background and thier reply to each individual question? I got the percentages in excel for the overall responses (221) and individually by grouping them according to language. The percentages show which group was the most to agree or disagree with a certain question, but how to do it with statistics please?

[@DavidDunaetz]

+Zainab Jaafar Here's a brief, simple way to compare these groups. It's not as rigorous as it could be, but it will work. Recode your data so that disagree = 0, neutral = 1, agree = 2. Compare the means of each possible pair of groups using at two-sample (independent) t-test (see kzbin.info/www/bejne/oZ7aqGN7mtx8ppI ).

[@eddyrealon1105]

How do you do regression using nonparametric variables (i.e., ordinal data)? For instance, I have maximum price buyers are willing to pay for a product as the dependent variable (Y) and the factors considered [ranked from very important (1) to not important (4)] as the independent variables (X1, X2, X3...). Wondering how I can do analyses on such data. Thank you in advance!

[@DavidDunaetz]

That's a good question. I'd be tempted to just treat the independent variables as continuous variables. But if you Google around, you can probably find other ways of doing it.

[@eddyrealon1105]

@@DavidDunaetz If ever I'm gonna try to treat them as continuous variables, should I label "very important" as the highest in the scale (4) and the "not important" as the lowest (1) instead? I guess this scale important because the coefficients would differ. Thank you very much for your response!

[@DavidDunaetz]

@@eddyrealon1105 Yes, that is how I would label them.

[@rebeccaemmons5681]

How would do a correlational student between student state reading scores and race, economically disadvantaged and disability? Thanks for all your videos

[@DavidDunaetz]

For a simple Pearson correlation, you need to have each variable in a column. For categorical variables, you need to have dichotomous variables such White = 1, non-White = 0. You would need to create such a variable for each race you want to look at, e.g., Black = 1, non-Black = 0. You would also create a variable for economic situation, non-advantaged = 0, advantaged = 1, and for disability, non-disability = 0, disability = 1. You can create a table of correlations as described on this video: kzbin.info/www/bejne/maTafYN4jc2GjdU (or the equivalent one for Macs: kzbin.info/www/bejne/ina4eaegh5KJpKs ). Because these variables are all correlated with each other, it would be more interesting to do a multivariate analysis with multiple regression than a simple bivariate analysis with Pearson correlations. Here is a video on multiple regression: kzbin.info/www/bejne/n5TMoZqwbblqmtk

[@rebeccaemmons2423]

Thank you!

[@jennlynn6208]

I really appreciate your videos. I don't understand why the second hypothesis isn't significant if the absolute value of r is .24 and the p value is .13 one tailed. Could you please explain?

[@DavidDunaetz]

A correlation is significant if p < .05. In this case, p > .05 (because p = .13), so it's not significant.

[@jennlynn6208]

@DavidDunaetz as long as the p value is below .05, then it is significant. It doesn't matter if r is less than p value if it isn't below .05, correct?

[@DavidDunaetz]

@@jennlynn6208 Yes, that's correct. Significance only depends on the p value.

[@josephinemarcial7512]

What is the difference between the correlation option and the regression option in the data tools in Excel.

[@DavidDunaetz]

Correlation looks at how two variables are related to each other (e.g., how one variable predicts the other). Regression (or multiple regression) looks how several variables combine to predict another. If you Google around, you can find my videos on regression.

[@mofwzyy]

Can I make a regression between two variable based on 7 values for each one ?. Their values are estimated based on secondary data for 7 historical years. Firstly, I am using Microsoft Excel Software Package 2016 to form and simulate the table function (Graphical effect) based on the historical data of 7 years of these two variable. Then, extrapolation process was made to form the shape of effect graph. In order to enhance this line graph, I just want to measure the significance of relationship between of them.

[@DavidDunaetz]

You can do the regression analysis with only 7 data points, but the coefficients might not be significant unless there is a very strong or consistent relationship.

[@mofwzyy]

@@DavidDunaetz Coefficients = 0.74 P-value Intercept (Independent Variable) = 0.32 Dependent variable = 0.01 Is there a significance of relationship ?

[@DavidDunaetz]

@@mofwzyy The p-value for the intercept is usually not important. What counts is the p value for your independent variable (listed right below the coefficient). So, if p = .01 for your independent variable, then the relationship is signfiicant.

[@charleslevielagman3937]

How do you know which tailed test you are going to use?

[@DavidDunaetz]

It depends on your hypothesis. If you hypothesize that two groups A and B have different averages but you're not hypothesizing which is greater, then you use a two-tailed test. If you hypothesize that the average of A is greater than the average B, then you use a one-tailed test.

[@mofwzyy]

I just wanted to know the name of following process: If Variable A = 10, Variable B= 15 and Variable C= 20. The sum is 45. If I want to identify the weight of each one. weight for Variable A = 10/45 = 0.22 and so on. the final sum of these weights will be equal to 1 what is the name and equation of this process ? standardization or normalisation or optimisation or what ?

[@DavidDunaetz]

This process is called "weighting" each variable.

[@mofwzyy]

@@DavidDunaetz Thank you Dr. David

[@aloriealmaden6329]

I thought Multiple R is the r-value and P-value is the p-value itself. Anyways, thanks for sharing.

[@DavidDunaetz]

The Multiple R is the absolute value of r; the Multiple R is always positive. (This is when there are only two variables; when there are more than two variables, the Multiple R is something else.) The p-value shown in the ANOVA table is always the two-tailed p-value for r. If you want the one-tailed p-value, you need to divide it by 2.