We help IT Professionals succeed at work.

# What is Access Equivalent of RSQ Function in Excel?

on
Medium Priority
794 Views
Hi All --

My niece called me (her Auntie Access Developer) asking me how to do a RSQ in Access... I found a bit of info on Pearson's Correlation, but it was a bit dated.

Can anyone tell me how to do this?  She says, " We have the known X and Y values but cannot figure out how to use the RSQ function in Access that Excel has.  It would also be helpful if we could calculate both the linear and logarithmic r-squared value. "

Kids are so smart these days...

Ronda
Comment
Watch Question

## View Solutions Only

CERTIFIED EXPERT
Top Expert 2006
Commented:
I am now aware of any. Unless your excellent in School Maths :) u could code it yourself

I just checked wikipedia - Blimey!!!   http://en.wikipedia.org/wiki/Correlation

Alternative is to graph in Excel then use excel automation to calculate it. Nasty though, not fun sorting out graphs in excel via automation. I havent done that before

Not the solution you were looking for? Getting a personalized solution is easy.

Database Developer
CERTIFIED EXPERT
Commented:
Sorry - don't know anything about maths... (ahem.. looks nervously around the room incase anyone realizes and *makes* me do it :-S)

:-p
As interesting as evaluating that function (maths function as opposed to VBA) could be - I'd look to avoid the work - and just reference the Excel function from Access.

Function fGetExcelRSQ(arrX, arrY)

Dim objExcel As New Excel.Application

fGetExcelRSQ = objExcel.WorksheetFunction.RSq(varrX, varrY)

objExcel.Quit
Set objExcel = Nothing

End Function

For it to be truly useful on a performance level though - you'd really want to have the Excel object as a module level object variable - that's opened and persisted as long as necessary for each subsequent call - and then just use the function whenever you wanted.
CERTIFIED EXPERT
Top Expert 2006

Commented:
LPurvis has given the example of what I was talking about - excel automation
I actually thought it would be more complicated, oh well

Just to add to LPurvis's post, u had better add a reference to Microsoft Excel object library or use latebinding - create as object

Commented:
Thanks to both of you for your post -- I appreciate the input.

I'll pass this on... although I still don't know what the *&%! Pearson's Coefficient is supposed to ultimately do, I did learn something out of this for myself:  I have worked with some basic Excel automation, I didn't know you could create an Access function based on an Excel Function like you did, LPurvis.  I'll have to play around with that one... maybe with an Excel function I understand!

;-) Ronda
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile