Find Correlation between 2 items in Excel

[Smoke922]

Hello all,

New to the forum, hope I am posting this in the right area.

As the title states, I need to find correlation between service levels and call types. The data is in the same workbook on two different tabs. Tab1 has 1 entry for each date (multiple dates) and a corresponding service level for that date. There are several rows, but here is an Example of 1 row:

Column A Column B
8/29/2018 84%


Tab2 has a multiple entries per date, each entry has a different category for a call type, and there are several different call types. Again, there are a lot of rows of data, but here is an example:

Column A Column B
8/29/2018 Call Type3
8/29/2018 Call Type1
8/29/2018 Call Type9

Any help would be appreciated!!!

[AliGW]

Welcome to the forum!

So what exactly are you aiming for?

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[AliGW]

Sorry - we can't help until you tell us what you are trying to achieve. So far, you've given us two samples of data - how are you trying to correlate them?

[Smoke922]

Thank you for the replies. I will work on getting a sample attached, i am away from my computer for a bit. If i upload it, will the file only be accessible by myself and moderators? There will not be any condfidential information, just wondering if the doc is accessible to the entire internet community or not.

I need to find out if a reduction in service levels is correlated with frequency of any of the types of calls.

[AliGW]

Yes, your file will be available to all - this is a public forum, which is why you need to heed point 3 in my instructions.

[MrShorty]

I would add that it will also help if you help us understand how much of this is an Excel question ("I know how to perform an ANOVA [or other chosen hypothesis test], but I do not know how to perform the test in Excel.") and how much is a more generic statistics question ("I don't know how anyone using any software package performs this kind of test."). We are pretty good at programming things into Excel, but we are not always as knowledgeable about the statistics side of these questions.

Assuming this is mostly an Excel question and not a statistics question:
Excel's built in functions by category (look in the statistics category): https://support.office.com/en-us/art...1-63f26a86c0eb
Questions of "correlation" often involve the Pearson correlation coefficient, which can be calculated using the PEARSON() or CORREL() functions: https://support.office.com/en-us/art...b-239d7b68ca92
If this ends up being more of an ANOVA type test, this tutorial explains how to perform an ANOVA test using Excel's analysis toolpak: https://www.excel-easy.com/examples/anova.html
When you decide which statistic or test you want to calculate, pay attention to how the data should be arranged in the spreadsheet for that test. Where you have two different data tables with different contributions to the problem, I would not be surprised if a big part of this question ends up being about how to move data around the spreadsheet or summarize the data in one of the tables so that you can calculate the desired correlation coefficient or test statistic.

[Smoke922]

OK, I have attached a sample workbook. Everything is "dummy" data. This is much more of an excel question rather than a statistics question.

[shg]

Like this?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Correl:	0.270	(0.139)	(0.171)	(0.257)	(0.140)	(0.145)	0.140	(0.158)	(0.471)	(0.210)		C1: =CORREL($B$3:$B$24, C3:C24)
2	Date	%	Random1	Random2	Random3	Random4	Random5	Random6	Random7	Random8	Random9	Random10
3	1/2/2018	96.86%	177	92	133	31	19	22	15	2	7	1		C3: =COUNTIFS(Sheet2!$A$2:$A$11438, Sheet1!$A3, Sheet2!$B$2:$B$11438, Sheet1!C$2)
4	1/3/2018	98.35%	174	99	137	21	35	30	23	1	5	2
5	1/4/2018	93.31%	145	90	130	35	30	24	21	1	6	0
6	1/5/2018	90.16%	151	111	130	26	34	23	12	1	8	1
7	1/8/2018	99.32%	169	87	143	31	44	25	28	0	6	1
8	1/9/2018	97.45%	148	78	145	34	38	43	20	2	4	2
9	1/10/2018	95.55%	150	93	153	37	40	27	14	1	6	1
10	1/11/2018	91.35%	126	95	156	26	44	34	23	0	12	0
11	1/12/2018	87.84%	112	90	180	30	33	23	22	3	5	1
12	1/15/2018	96.25%	97	75	78	24	22	14	10	1	3	0
13	1/16/2018	96.83%	156	88	152	30	22	30	19	2	4	0
14	1/17/2018	89.75%	143	97	140	36	38	30	17	4	7	0
15	1/18/2018	89.57%	148	79	152	28	41	26	22	2	9	1
16	1/19/2018	85.11%	171	87	141	32	38	36	19	1	9	1
17	1/22/2018	97.35%	185	127	132	32	41	24	34	2	8	2
18	1/23/2018	92.19%	163	123	143	48	39	33	24	4	10	1
19	1/24/2018	96.65%	169	90	154	35	33	36	24	1	3	3
20	1/25/2018	83.59%	132	101	142	41	49	34	19	1	8	6
21	1/26/2018	97.12%	130	87	135	18	34	26	12	1	9	0
22	1/29/2018	89.84%	169	125	128	30	37	28	24	2	8	2
23	1/30/2018	95.38%	149	106	153	34	74	30	24	3	7	1
24	1/31/2018	96.43%	161	97	156	36	48	32	26	2	5	4

[Smoke922]

That looks like it may work. I am trying to correlate lower Service level percentages (ColumnB) as they are related to frequency of call types. So, when the % in Column B drops, are there specific types of calls (Random1-Random10) that might be correlated with the reduction? Not sure of the chart you attached is answering that or not. I am Assuming that the top row is the correlation coefficient?

[shg]

Correct. It's the correlation of the count of Randomx with whatever the percentage means.

[Smoke922]

OK, so to clarify: In this case, Random1 has a positive correlation of 0.27, meaning that there is at least some correlation between increased frequency of this call type and lower service level percentages?

Also, how would I go about programming this into Excel so I can run it on the real workbook?

[shg]

In this case, Random1 has a positive correlation of 0.27, meaning that there is at least some correlation between increased frequency of this call type and lower service level percentages?

Yes.

Also, how would I go about programming this into Excel so I can run it on the real workbook?

Don't understand the question. That does the analysis you described on the data you posted using the formulas shown.

[MrShorty]

Random1 has a positive correlation of 0.27, meaning that there is at least some correlation between increased frequency of this call type and lower service level percentages?

I don't think we can say this rigorously without a statistical test that says for sure that r=0.27 is not statistically equivalent to 0. Following the t-test approach used here (http://www.janda.org/c10/Lectures/to...nificanceR.htm ), I calculate a t score of 1.25, and the critical value (assuming alpha of 0.1) is 1.72. Because our observed t is less than the critical value, we cannot conclude that r=0.27 is significantly different from r=0 (no correlation).
random9 might be the only column in the example that is statistically different from r=0.

[Smoke922]

Edit: Sorry, forgot to reply with quote.

SHG stated: Don't understand the question. That does the analysis you described on the data you posted using the formulas shown.

Thank you, appreciate all of your help. My data I attached was just a small sample and I need to be able to duplicate the process you completed on the full workbook. I was looking for the process of how you go about programming this into the workbook.

[AliGW]

Are you looking for a VBA solution, then? If so the thread is in the wrong place, but I can move it for you. Please confirm. Also your thread title mentioned nothing about coding a solution.

[Smoke922]

Are you looking for a VBA solution, then? If so the thread is in the wrong place, but I can move it for you. Please confirm. Also your thread title mentioned nothing about coding a solution.

Ok, thank you. I am looking for information on how to program the solution into my full workbook. I apologize if this is posted on the wrong area and feel free to move it if needed.

[MrShorty]

I, too, am not sure what your question is. The overall flow of logic to shg's solution (as I see it) is:

1) In order to correlate against call type frequency, you need to count how many calls of each type occurred on each day. In his solution, he used a COUNTIFS() function. He was also careful in his use of relative and absolute references for easy copying. What part of using a COUNTIFS() function are you having trouble adapting to your real spreadsheet? (help file, if needed: https://support.office.com/en-us/art...c-aa8c2a866842 )
2) With the frequency data counted, he then used the CORREL() function to get the Pearson correlation coefficient r. Again, he paid attention to relative and absolute references so it was easy to copy. Is there something about the CORREL() function that you are having trouble implementing in your spreadsheet?

[Smoke922]

OK, I think I have this one figured out now. Thank you all for your help - I'm sure I will post more questions later!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.