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.

[TABLE="width: 451"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Monday, April 2, 2012[/TD]
[TD]11%[/TD]
[TD]8.2[/TD]
[TD]7.4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4/3/2012[/TD]
[TD]4%[/TD]
[TD]7.8[/TD]
[TD]7.5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4/4/2012[/TD]
[TD]6%[/TD]
[TD]7.77[/TD]
[TD]7.31[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4/5/2012[/TD]
[TD]2%[/TD]
[TD]7.55[/TD]
[TD]7.37[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4/9/2012[/TD]
[TD]19%[/TD]
[TD]7.89[/TD]
[TD]6.62[/TD]
[TD] -19%[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Day	%	Max	Min	Running %

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6/17/2019[/TD]
[TD="align: right"]11%[/TD]
[TD="align: right"]8.2[/TD]
[TD="align: right"]7.4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6/18/2019[/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"]7.8[/TD]
[TD="align: right"]7.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6/19/2019[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]7.77[/TD]
[TD="align: right"]7.31[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6/20/2019[/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]7.55[/TD]
[TD="align: right"]7.37[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6/21/2019[/TD]
[TD="align: right"]19%[/TD]
[TD="align: right"]7.89[/TD]
[TD="align: right"]6.62[/TD]
[TD="align: right"]-19%[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6/22/2019[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]8.93[/TD]
[TD="align: right"]7.93[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6/23/2019[/TD]
[TD="align: right"]21%[/TD]
[TD="align: right"]7.61[/TD]
[TD="align: right"]6.29[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6/24/2019[/TD]
[TD="align: right"]35%[/TD]
[TD="align: right"]8.38[/TD]
[TD="align: right"]6.2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6/25/2019[/TD]
[TD="align: right"]3%[/TD]
[TD="align: right"]6.63[/TD]
[TD="align: right"]6.44[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6/26/2019[/TD]
[TD="align: right"]18%[/TD]
[TD="align: right"]7.16[/TD]
[TD="align: right"]6.07[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]6/27/2019[/TD]
[TD="align: right"]31%[/TD]
[TD="align: right"]8.03[/TD]
[TD="align: right"]6.11[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]6/28/2019[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"]8.68[/TD]
[TD="align: right"]6.96[/TD]
[TD="align: right"]-30%[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]6/29/2019[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]8.92[/TD]
[TD="align: right"]7.86[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]6/30/2019[/TD]
[TD="align: right"]1%[/TD]
[TD="align: right"]6.13[/TD]
[TD="align: right"]6.06[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=C2/D2-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=C3/D3-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=C4/D4-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=C5/D5-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B6[/TH]
[TD="align: left"]=C6/D6-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B7[/TH]
[TD="align: left"]=C7/D7-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]=C8/D8-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B9[/TH]
[TD="align: left"]=C9/D9-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]=C10/D10-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]=C11/D11-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B12[/TH]
[TD="align: left"]=C12/D12-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B13[/TH]
[TD="align: left"]=C13/D13-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B14[/TH]
[TD="align: left"]=C14/D14-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B15[/TH]
[TD="align: left"]=C15/D15-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=IF(TEXT(A6,"dddd")="Friday",SMALL(D2:D6,1)/LARGE(C2:C6,1)-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E7[/TH]
[TD="align: left"]=IF(TEXT(A7,"dddd")="Friday",SMALL(D3:D7,1)/LARGE(C3:C7,1)-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E8[/TH]
[TD="align: left"]=IF(TEXT(A8,"dddd")="Friday",SMALL(D4:D8,1)/LARGE(C4:C8,1)-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E9[/TH]
[TD="align: left"]=IF(TEXT(A9,"dddd")="Friday",SMALL(D5:D9,1)/LARGE(C5:C9,1)-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E10[/TH]
[TD="align: left"]=IF(TEXT(A10,"dddd")="Friday",SMALL(D6:D10,1)/LARGE(C6:C10,1)-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E11[/TH]
[TD="align: left"]=IF(TEXT(A11,"dddd")="Friday",SMALL(D7:D11,1)/LARGE(C7:C11,1)-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E12[/TH]
[TD="align: left"]=IF(TEXT(A12,"dddd")="Friday",SMALL(D8:D12,1)/LARGE(C8:C12,1)-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E13[/TH]
[TD="align: left"]=IF(TEXT(A13,"dddd")="Friday",SMALL(D9:D13,1)/LARGE(C9:C13,1)-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E14[/TH]
[TD="align: left"]=IF(TEXT(A14,"dddd")="Friday",SMALL(D10:D14,1)/LARGE(C10:C14,1)-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E15[/TH]
[TD="align: left"]=IF(TEXT(A15,"dddd")="Friday",SMALL(D11:D15,1)/LARGE(C11:C15,1)-1,"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

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

[TABLE="width: 455"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Friday, March 30, 2012[/TD]
[TD]17%[/TD]
[TD]8.24[/TD]
[TD]7.06[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday, April 2, 2012[/TD]
[TD]11%[/TD]
[TD]8.2[/TD]
[TD]7.4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/3/2012[/TD]
[TD]4%[/TD]
[TD]7.8[/TD]
[TD]7.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/4/2012[/TD]
[TD]6%[/TD]
[TD]7.77[/TD]
[TD]7.31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thursday, April 5, 2012[/TD]
[TD]2%[/TD]
[TD]7.55[/TD]
[TD]7.37[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Monday, April 9, 2012[/TD]
[TD]19%[/TD]
[TD]7.89[/TD]
[TD]6.62[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/10/2012[/TD]
[TD]4%[/TD]
[TD]7.23[/TD]
[TD]6.97[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/11/2012[/TD]
[TD]8%[/TD]
[TD]7.53[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/12/2012[/TD]
[TD]7%[/TD]
[TD]7.56[/TD]
[TD]7.08[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday, April 13, 2012[/TD]
[TD]10%[/TD]
[TD]7.69[/TD]
[TD]7[/TD]
[TD]-16%[/TD]
[/TR]
[TR]
[TD]Monday, April 16, 2012[/TD]
[TD]6%[/TD]
[TD]7.16[/TD]
[TD]6.73[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/17/2012[/TD]
[TD]5%[/TD]
[TD]7.15[/TD]
[TD]6.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/18/2012[/TD]
[TD]7%[/TD]
[TD]7.3[/TD]
[TD]6.82[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/19/2012[/TD]
[TD]4%[/TD]
[TD]7.29[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Friday, April 20, 2012[/TD]
[TD]2%[/TD]
[TD]7.19[/TD]
[TD]7.02[/TD]
[TD]-8%[/TD]
[/TR]
</tbody>[/TABLE]

 

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