Ongoing Differences

[Droriley]

I have a data table of dates and values, which a sample of it is attached below.

I would like to make different manipulations on it, all are comparative between, for instance, every Thursday and its preceded Wednesday, every Thursday and its preceded Thursday, etc.

For Example:
Next to each Thursday I want the difference between the corresponding value and the corresponding value of the former Thursday.

Date Index
31/05/2018 1512.44
30/05/2018 1511.69
29/05/2018 1524.27
28/05/2018 1528.07
27/05/2018 1516.32
24/05/2018 1511.5
23/05/2018 1519.95
22/05/2018 1528.22
21/05/2018 1516.37
17/05/2018 1502.42
16/05/2018 1491.85
15/05/2018 1487.04
14/05/2018 1485.21
13/05/2018 1472.19
10/05/2018 1469.98
09/05/2018 1464.42
08/05/2018 1474.74
07/05/2018 1476.13
06/05/2018 1468.91
03/05/2018 1476.2
02/05/2018 1463.83
01/05/2018 1465.24
30/04/2018 1473.96
29/04/2018 1466.89
26/04/2018 1459.83
25/04/2018 1480.7
24/04/2018 1480.67
23/04/2018 1491.82
22/04/2018 1484.42
17/04/2018 1476.26
16/04/2018 1471.43
15/04/2018 1461.65
12/04/2018 1439.44
11/04/2018 1441.07
10/04/2018 1418.91
09/04/2018 1416.93
08/04/2018 1391.67
04/04/2018 1423.86
03/04/2018 1431.07
02/04/2018 1440.09
01/04/2018 1436.22
29/03/2018 1434.43
28/03/2018 1448.5
27/03/2018 1438.36
26/03/2018 1427.67
25/03/2018 1470.44
22/03/2018 1483.42
21/03/2018 1488.24

[AliGW]

Thanks for the sample source data. Ideally we also need some sample result data - where should it go? What should it look like? Attaching a workbook would be of more use with some expected results manually mocked up.

[Droriley]

Thanks for the sample source data. Ideally we also need some sample result data - where should it go? What should it look like? Attaching a workbook would be of more use with some expected results manually mocked up.

I am facing issues attaching a file from some reason.
I'll try to demonstrate below:
Capture.JPG

[AliGW]

Workbook, please, not screenshot. Thanks!

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.

[Droriley]

Hopefully now the file is upload successfully. Thanks for the instructions.

I wish to have a formula for the column that will output the same result. Note: not always the gap between following Thursdays is equal.

[AliGW]

Thanks - that's great. Someone will take a look.

[AliGW]

In E2 copied down:

=IF(C2=5,B2-INDEX(B3:B$49,MATCH(C2,C3:C$49,0)),"")

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	Date	Index	weekday		Wanted result:
2	31/05/2018	1512.44	5		0.94
3	30/05/2018	1511.69	4
4	29/05/2018	1524.27	3
5	28/05/2018	1528.07	2
6	27/05/2018	1516.32	1
7	24/05/2018	1511.5	5		9.08
8	23/05/2018	1519.95	4
9	22/05/2018	1528.22	3
10	21/05/2018	1516.37	2
11	17/05/2018	1502.42	5		32.44
12	16/05/2018	1491.85	4
13	15/05/2018	1487.04	3
14	14/05/2018	1485.21	2
15	13/05/2018	1472.19	1
16	10/05/2018	1469.98	5		-6.22
17	09/05/2018	1464.42	4
18	08/05/2018	1474.74	3
19	07/05/2018	1476.13	2
20	06/05/2018	1468.91	1
21	03/05/2018	1476.2	5		16.37
22	02/05/2018	1463.83	4
23	01/05/2018	1465.24	3
24	30/04/2018	1473.96	2
25	29/04/2018	1466.89	1
26	26/04/2018	1459.83	5		20.39

Sheet: Sheet1

[Droriley]

Wow, brilliant. Thanks for the reply and the speed of it

[AliGW]

You're welcome! Having the workbook makes it so much quicker.

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

[Droriley]

Absolutely will do.
Can you kindly also indicate why it wouldn't apply on the original file? I'd be happy if you point the error.

[AliGW]

Look a bit more closely at the original!

You have got the arrays wrong. They need to start on the row below:

=IF(I5=5,B5-INDEX(B6:B$742,MATCH(I5,I6:I$742,0)),"")

[Droriley]

Bellissimo. Thanks!!!

[AliGW]

Glad to have helped!