Solved

Coefficient of Correlation in pivot table

Posted on 2006-06-13
8
2,840 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
[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
  • 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

688 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