Coefficient of Correlation in pivot table

Posted on 2006-06-13
Medium Priority
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
Question by:jakubkaiser
  • 2
  • 2
LVL 58

Accepted Solution

harfang earned 1000 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?


Author Comment

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

LVL 58

Expert Comment

ID: 16931269

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).


LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17034534

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
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17034742

Aslo check out FMSINC's "Total Access Statistics"


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

But beware: $500.00US !

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

621 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