Correlation formula

[Alexmedft]

I am trying to find any correlation between Day Ahead prices and real time prices. I tried the pearson formula but do not think I applied it correctly. For example I want a formula that looks at each day ahead hour and finds a correlation with real time outcome. IF Day Ahead Hour1 (cells b3;b61) clears over real time Hour 1 (cells h3;h61) what is the correlation. What does Hour 1 in day ahead need to be for it to clear over hour 1 in real time. I am using microsoft 365. Please see the tab labeled Sheet1.

Thnk you.

[AliGW]

There are no expected results mocked up. Just providing a data dump with no annotations or explanations in the workbook is not enough.

[AliGW]

Based on your duplicate thread (now closed), in AC4 copied down:

=INDEX($B$3:$Y$23,MATCH(AB4,$A$3:$A$23,0),MATCH(AC$3,$B$2:$Y$2,0))-INDEX($B$26:$Y$45,MATCH(AB4,$A$26:$A$45,0),MATCH(AC$3,$B$25:$Y$25,0))

[bsalv]

see row 47:49 =CORRELATIE(H$26:H$45;H$3:H$22)

[Alexmedft]

So what does the slope and intercept provide. I see what the correlation is doing but not sure of the purpose for slope and intercept.

[AliGW]

What about post #3???

[Alexmedft]

Post #3 just returned the spread between Day ahead and real time. I am trying to find out why those three days were positive by looking back and seeing if i can find any correlation in the other hours that may give me any in sight.

[AliGW]

You posted a workbook to the closed thread. It showed the results that you wanted (which was what I asked for). My formula gives EXACTLY those results.

You are wasting our time, one way and another.

[Alexmedft]

I am sorry I am wasting your time. I asked a question and I am sorry if you interpreted it wrong but too say I am wasting your time is very unprofessional. I came on here because I get amazing help from the community and I am not trying to waste anyone's time.

[AliGW]

I asked you to show us what you wanted. You then posted a workbook containing this (and nothing else new):


AliGW on MS365 Beta Channel (Windows 11) 64 bit

	AB	AC
2		Day Ahead over Real time price spread
3		7
4	8/1/23	-3.82
5	8/2/23	-5.32
6	8/3/23	-1.48
7	8/4/23	-1.55
8	8/5/23	-2.44
9	8/6/23	-3.11
10	8/7/23	-2.82
11	8/8/23	1.22
12	8/9/23	-0.96
13	8/10/23	-6.07
14	8/11/23	-2.38
15	8/12/23	-2.70
16	8/13/23	-3.89
17	8/14/23	-1.94
18	8/15/23	-2.37
19	8/16/23	3.16
20	8/17/23	2.73
21	8/18/23	-0.99
22	8/19/23	-1.17
23	8/20/23	-5.97

Sheet: Sheet1

I then gave you a formula to produce exactly those results. Then you tell me that I have misinterpreted your requirements.

So why did you show this table above if that's not what you want? And WHERE precisely have you mocked up what you DO WANT?

[Alexmedft]

I asked to find the correlation if any for the reason for the three positive days. I never asked to find me another formula to find the spread between day ahead and real time. I did not aske to find the spread between the day ahead and real time prices.

[AliGW]

But I asked you to provide a worksheet showing what you wanted and you provided a workbook containing that table. WHERE in that workbook have you mocked up the results you are looking for?

[bsalv]

see graph : the X-axe = day ahead and the Y-axe = real time values for column H = hour 7:00
you can add a lineair trendline in the graph and ask the coordinates, they are exactky the intercept and the slope and R2
So for the 7:00-data >>> real values = 0.976 * day ahead + 2.55 with R2=0.58

[Alexmedft]

Very interesting. Thank you I will have to play around with this but it is very helpful.