Calculate running percentage change highs and lows every five days

[xeven_]

I have data in coulmns C and D. I want to calculate the percent difference between th ehighest and lowest valueevery five days and have that value populate in the adjacent column/cell E. Is there a formula to help me do this? As in this example the percent change form the highest vale 8.2 to the lowest 6.62 is -19%. I was hoping to get a formula that can populate in E and scroll down to keep a running analysis of every five days. This is daily data with a weekly hopefully relevent metric.

Monday, April 2, 2012	11%	8.2	7.4
4/3/2012	4%	7.8	7.5
4/4/2012	6%	7.77	7.31
4/5/2012	2%	7.55	7.37
4/9/2012	19%	7.89	6.62	-19%

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

 

[Aryatect]

Hi,

Below formula applied from Friday and below should work:

=IF(TEXT(A6,"dddd")="Friday",SMALL(D2:D6,1)/LARGE(C2:C6,1)-1,"")

This is shown below:

	A	B	C	D	E
1	Day	%	Max	Min	Running %
2	6/17/2019	11%	8.2	7.4
3	6/18/2019	4%	7.8	7.5
4	6/19/2019	6%	7.77	7.31
5	6/20/2019	2%	7.55	7.37
6	6/21/2019	19%	7.89	6.62	-19%
7	6/22/2019	13%	8.93	7.93
8	6/23/2019	21%	7.61	6.29
9	6/24/2019	35%	8.38	6.2
10	6/25/2019	3%	6.63	6.44
11	6/26/2019	18%	7.16	6.07
12	6/27/2019	31%	8.03	6.11
13	6/28/2019	25%	8.68	6.96	-30%
14	6/29/2019	13%	8.92	7.86
15	6/30/2019	1%	6.13	6.06

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula B2 =C2/D2-1 B3 =C3/D3-1 B4 =C4/D4-1 B5 =C5/D5-1 B6 =C6/D6-1 B7 =C7/D7-1 B8 =C8/D8-1 B9 =C9/D9-1 B10 =C10/D10-1 B11 =C11/D11-1 B12 =C12/D12-1 B13 =C13/D13-1 B14 =C14/D14-1 B15 =C15/D15-1 E6 =IF(TEXT(A6,"dddd")="Friday",SMALL(D2:D6,1)/LARGE(C2:C6,1)-1,"") E7 =IF(TEXT(A7,"dddd")="Friday",SMALL(D3:D7,1)/LARGE(C3:C7,1)-1,"") E8 =IF(TEXT(A8,"dddd")="Friday",SMALL(D4:D8,1)/LARGE(C4:C8,1)-1,"") E9 =IF(TEXT(A9,"dddd")="Friday",SMALL(D5:D9,1)/LARGE(C5:C9,1)-1,"") E10 =IF(TEXT(A10,"dddd")="Friday",SMALL(D6:D10,1)/LARGE(C6:C10,1)-1,"") E11 =IF(TEXT(A11,"dddd")="Friday",SMALL(D7:D11,1)/LARGE(C7:C11,1)-1,"") E12 =IF(TEXT(A12,"dddd")="Friday",SMALL(D8:D12,1)/LARGE(C8:C12,1)-1,"") E13 =IF(TEXT(A13,"dddd")="Friday",SMALL(D9:D13,1)/LARGE(C9:C13,1)-1,"") E14 =IF(TEXT(A14,"dddd")="Friday",SMALL(D10:D14,1)/LARGE(C10:C14,1)-1,"") E15 =IF(TEXT(A15,"dddd")="Friday",SMALL(D11:D15,1)/LARGE(C11:C15,1)-1,"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[xeven_]

Awesome! Hey if the higher value such as 8.2 on 6/17/19 came first and the lower value 6.62 later can it be made to reflect a decrease (negative % change) instead of positive? Trying to line graph it isn't working to well with the negative numbers

 

[xeven_]

This is what I have which is pretty close. The only thing is my excel 2016 doesnt want to graph negative values. Perhaps I can reverse the large and small in the code to change the values to positive number but then the actual data string will be false as when working with percents the value is different when going from the higher number to the lowerthan it is when going from lower to higher.. sigh.....

Friday, March 30, 2012	17%	8.24	7.06
Monday, April 2, 2012	11%	8.2	7.4
4/3/2012	4%	7.8	7.5
4/4/2012	6%	7.77	7.31
Thursday, April 5, 2012	2%	7.55	7.37
Monday, April 9, 2012	19%	7.89	6.62
4/10/2012	4%	7.23	6.97
4/11/2012	8%	7.53	7
4/12/2012	7%	7.56	7.08
Friday, April 13, 2012	10%	7.69	7	-16%
Monday, April 16, 2012	6%	7.16	6.73
4/17/2012	5%	7.15	6.8
4/18/2012	7%	7.3	6.82
4/19/2012	4%	7.29	7
Friday, April 20, 2012	2%	7.19	7.02	-8%

<colgroup><col><col span="3"><col></colgroup><tbody>
</tbody>

 

[xeven_]

Think I got it. I played with it a bit and modified it to: =IF(TEXT(A6,"dddd")="Friday",LARGE(D2:D6,1)/SMALL(C2:C6,1)-1,"")
This gives me the positive values in E and makes it where the chart and illustrate it visually. The higher to lower or lower to higher difference is likely still an issue but as I am mainly looking for major abnormalities it may not affect it too much.

Thanks again

 

[Aryatect]

Great that it worked out, Happy to help !!