Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

What is Access Equivalent of RSQ Function in Excel?

Posted on 2006-10-19
4
Medium Priority
?
723 Views
Last Modified: 2012-06-27
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...

Thanks in advance!

Ronda
0
Comment
Question by:Ronda-S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 200 total points
ID: 17771707
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
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 1800 total points
ID: 17772233
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17772245
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
0
 

Author Comment

by:Ronda-S
ID: 17789564
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
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question