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


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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

604 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