R2 value in a single cell, not set as an array

[ReferenceGoblin]

Hi all,

I was wondering if anyone knows how to get the r2 value (for linear and 2nd order polynomial) into a single cell? (I'm using 2010). I can get it on a graph easily enough, and I've figured out the array method with LINEST for getting it alongside with F values, standard deviation, and total number of squares. However, with that method its obviously a) taking up 5 cells and b) requires the ctrl+shift+enter to set each as an array.

My situation is that I have my data arranged in rows, with cells A to O holding the x values and cells and cells P to AD holding the y values. However, there are just over a 1000 rows of data and I need the r2 value for each in a method that I can just copy and paste in. Once I've got the page set up I'll be turning it into a template as I have about thirty sets of such data to run through.

I've got a feeling that I've missed something obvious, so if anyone can point me to the 'elephant in the room', I'll be very grateful!

All the best

[MrShorty]

My stat's is weak, but isn't the r2 value called the Pearson coefficient or something similar? In my version (2002), there's a Pearson() function and a rsq() function that I think should get you what you want.

You should also be able to nest LINEST into INDEX to extract the single value.