I would like to understand how people add the P value on a figure for means (Y axis) by age, volume or any other variable (x axis). How did they calculate the P value here? Please check the following figure:

enter image description here

(They draw scatter plot for FA by age in figure one and they have two P values for the control and for ASD). In those figures we have two P values? why and how?)

  • 2
    $\begingroup$ as it reads now, the question is impossible to answer. Thereis no natural p-value for "means by age". ANOVA and regression are both linear models, but ANOVA assumes a categorical predictor, regression a continuous predictor. How many data points do you have for each age group (you need more than one for an ANOVA to make even sense here)? And what is your exact question? I would not recommend doing ANOVAs in Excel. $\endgroup$ – jank Oct 15 '14 at 21:51
  • $\begingroup$ The presence of an "$r$" (correlation coefficient) is a strong clue. Doesn't the text explain the statistical methods? $\endgroup$ – whuber Oct 15 '14 at 23:12
  • $\begingroup$ The text explained how the method was done BUT my question her why we have two P values.Here is the original paper (openi.nlm.nih.gov/…) $\endgroup$ – goro Oct 15 '14 at 23:15
  • 1
    $\begingroup$ Did you notice that each panel includes two sets of data ("ASD" and "Control", distinguished by shapes of the points) and two fitted lines? $\endgroup$ – whuber Oct 15 '14 at 23:17
  • 1
    $\begingroup$ Your supposition that the p-values in the plot relate to a test for a difference in groups is wrong. In each case the p-value apparently goes with the immediately preceding quoted statistic ($r$) corresponding to the correlation for that group alone. $\endgroup$ – Glen_b Oct 16 '14 at 0:46

The following is an excerpt from Miles and Banyard's (2007) "Understanding and Using Statistics in Psychology --- A Practical Introduction" on "Calculating the exact significance of a Pearson correlation in MS Excel":

Inconveniently, this is not completely straightforward - Excel will not give us the exact p-value for any value of r. However, it will give the exact $p$-value for any value of $t$, and it’s not too hard to convert $r$ to $t$. The formula you need is this one:

enter image description here

And then you use the tdist() function in Excel. So, we have a value of $r = 0.44$, and $N = 19$. We can use Excel to turn the $r$ into $t$, so in the Excel sheet (at Cell A1, let’s say) we type:

=(0.44 * sqrt(19 – 2))/(sqrt(1-0.44^2))

This gives a value of $t = 2.02$. We then use the tdist() function to find the associated $p$. We need to tell Excel 3 things. First, the value of $t$, second, the degrees of freedom, which are equal to $N – 2 = 17$, and third, the number of tails – either 1 or 2, and we always use 2 tails. If the value from the first calculation is stored in cell A1, we can write: =tdist(A1, 17, 2) Which gives a result of $p = 0.059$.

Should you ever want to calculate a critical value for a Pearson correlation, the process is reversed. You first calculate the critical value for $t$, and then you convert this into $r$. Let’s say we wanted to know the critical value for a correlation for $p = 0.05$. We first find the value of $t$ that gives a $p$ of $0.05$. We use the excel function tinv(). We need to tell Excel two things, the probability that we are interested in, and the degrees of freedom. Into cell A1 We type: =tinv(0.05, 17) Excel tells us that the answer is $2.11$. We then need to turn that into a value of r. The formula is the reverse of the one above, which takes a bit of algebra, so we’ll tell you what it is:

enter image description here

We type the formula into Excel =A1/(SQRT(A1 * A1 + 19 - 2 )) And we get the answer that the critical value is 0.0456.


  1. "Understanding and Using Statistics in Psychology: A Practical Introduction" Google Books

  2. How to Calculate the P-Value & Its Correlation in Excel ehow

| cite | improve this answer | |
  • $\begingroup$ Thank you for doing this. It can now stand alone as a valuable contribution to CV. $\endgroup$ – gung - Reinstate Monica Oct 16 '14 at 2:43
  • 1
    $\begingroup$ Shouldn't this answer contain a reference to the original source of information? I see it was in the original answer but as it stands now it is not known anymore. Most of the answer (if not all) is just quoting this section of that book. $\endgroup$ – ddiez Oct 16 '14 at 3:05
  • 1
    $\begingroup$ Hi ddiez, The reference is "Understanding and Using Statistics in Psychology: A Practical Introduction" it is available online also you can find the same explaination for this method in ehow $\endgroup$ – goro Oct 16 '14 at 3:19
  • 3
    $\begingroup$ @malshikho Thanks for your contribution to CV. Please do not just copy and paste the content of other websites as answer. At least, you have to indicate the source of the content and quote it properly. $\endgroup$ – Bernd Weiss Oct 16 '14 at 4:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.