Solved

Coefficient of Correlation in pivot table

Posted on 2006-06-13
8
2,351 Views
Last Modified: 2008-02-01
Coefficient of correlation
I want to set up a pivot table with X and Y values where the pivot between those 2 values is the coefficient of correlation.
It is a geological table with coeff. of corr. between Gold and Copper etc.
My level of experties is intermediate in Access and rudimentary in VB.
Thanks for your help...Jakub
0
Comment
Question by:jakubkaiser
  • 2
  • 2
8 Comments
 
LVL 58

Accepted Solution

by:
harfang earned 250 total points
ID: 16909518
Hello jakubkaiser

This is not easily done in either a pivot table or a cross-tab query. The formula itself is simple:

Correll_G_C: (Avg(Gold*Copper)-Avg(Gold)*Avg(Copper))/StDevP(Gold)*StDevP(Copper)

The difficult part is getting that into a "pivot". Normally, each cell of a pivot contains the same calculation based on different subsets of the data (different groups of rows). You want different calculations in each cell on the entire data (differents pairs of fields).

If the values are stored in different fields, this is not possible. If they are stored vertically in a table indicating metal/value pairs for samples, it might be, although it's probably not the base solution in this case either.

Would you care to exlain whether you need these figures for futher computations or merely for display?

(°v°)
0
 

Author Comment

by:jakubkaiser
ID: 16931014
Thak you harfang

It is for display only, eventually in a graph, the diagonal line being the r value. However the display in 3 columns is a first step (in a calculated field).
Sorry for the late answer-I am not familial with the way the forum works

Jakub
0
 
LVL 58

Expert Comment

by:harfang
ID: 16931269
jakubkaiser

For display only, I guess you can simply create textboxes in a form or report, in the header or the footer, while leaving the detail section blank. You could also create a query calculating the needed correlations and then use that as a base for a form or report.

The problem is that this would be with fixed fields. I don't see any easy way to make that dynamic. On the other hand, if you anyways want to substitute the r values in place of the diagonal with 1's, then this might be your best bet.

How are your fields stored? Or do you need more help at all?


About this site: First of all, welcome! I didn't realize this was your first question (^v^)

Any member can jump in at any time to add comments. Some questions get a very high activity for a short time and are solved within minutes. Others take longer with days between comments. After a given period of inactivity, a clean-up procedure kicks in.

You "task" after asking a question is to aknowlege answers, ask for further information, give feedback on what worked and what didn't and generally keep this alive until you get your answer...

Also remember that we don't know anything about your database or the problem at hand. For example, you say "between Gold and Copper etc.", which could mean many things (five fixed minerals? 2500 dynamic componds at the moment?), or "display in 3 columns", wich is not explained (which columns? why three? are they static?). It's not a problem as such, but it's good to keep that in mind.

Finally, if the answers aren't useful, try to narrow down what you want. A report? A form? An export to Excel? ... and expand your explations about your data structure and data (with sample data and sample output, for example).

Cheers!

(°v°)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17034534
jakubkaiser,

Your data would have to be in a format similar to this:
Date      Metal      price
01/01/06      Gold      473
01/01/06      Copper      264
01/02/06      Gold      403
01/02/06      Copper      283
01/03/06      Gold      433
01/03/06      Copper      279
01/04/06      Gold      436
01/04/06      Copper      219
01/05/06      Gold      474
01/05/06      Copper      268
01/06/06      Gold      407
01/06/06      Copper      253
01/07/06      Gold      418
01/07/06      Copper      212
01/08/06      Gold      465
01/08/06      Copper      258
01/09/06      Gold      433
01/09/06      Copper      266
01/10/06      Gold      412
01/10/06      Copper      230
01/11/06      Gold      470
01/11/06      Copper      271
01/12/06      Gold      447
01/12/06      Copper      275


With this you could get a pivot table that looked like this:
Average of price      Metal            
Date      Copper      Gold      Grand Total
01/01/06      264      473      368.5
01/02/06      283      403      343
01/03/06      279      433      356
01/04/06      219      436      327.5
01/05/06      268      474      371
01/06/06      253      407      330
01/07/06      212      418      315
01/08/06      258      465      361.5
01/09/06      266      433      349.5
01/10/06      230      412      321
01/11/06      271      470      370.5
01/12/06      275      447      361
Grand Total256.5      439.25      347.875

This Pivot table shows the "average" Price by Day and Metal
Access does not contain a "Corellation" function for summaries in a Pivot Tables. (See harfangs Formula)

You could *Try* to add in that formula in as a Calculated Total or Detail Field, but that gets very complicated. And I doubt it would work anyway, based on the reasons harfang listed above.

Hope this helps in some small way as well
:)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17034742
jakubkaiser,

Aslo check out FMSINC's "Total Access Statistics"

http://www.fmsinc.com/products/statistics/productguide.htm#DataAnalysisFunctions

It contains lots of statistical function that *Might* get you close to what you want

But beware: $500.00US !
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now