FORMULA - dynamic price change

[edvrdsdvskj]

Hello Everyone!

In row 3:3 is static data when this calculation has been started, consequently there are no any formulas in columns D:G
The divider in column D formulas (=(B4/B3)-100%) should be dynamic based on the conditions: differences of the prices are less than or equal to -7% or greater than or equal to +7%.
For example in D4:D11 the divider is B3, in D12:D23 - B11 etc.

Any ideas how to solve it?

Book1
	A	B	C	D	E	F	G
1	Period	Hot Rolled Coil		±∆%	±∆%	±∆%	Comment
2		Highest	Lowest	Highest	Lowest	AVG
3	2017-06-01	545	510
4	2017-07-01	525	490	-3.67%	-3.92%	-3.80%
5	2017-08-01	525	490	-3.67%	-3.92%	-3.80%
6	2017-09-01	555	520	+1.83%	+1.96%	+1.90%
7	2017-10-01	560	525	+2.75%	+2.94%	+2.85%
8	2017-11-01	560	525	+2.75%	+2.94%	+2.85%
9	2017-12-01	560	525	+2.75%	+2.94%	+2.85%
10	2018-01-01	580	545	+6.42%	+6.86%	+6.64%
11	2018-02-01	595	560	+9.17%	+9.80%	+9.49%	price increase
12	2018-03-01	615	580	+3.36%	+3.57%	+3.47%
13	2018-04-01	615	580	+3.36%	+3.57%	+3.47%
14	2018-05-01	615	580	+3.36%	+3.57%	+3.47%
15	2018-06-01	600	565	+0.84%	+0.89%	+0.87%
16	2018-07-01	595	560	0%	0%	0%
17	2018-08-01	595	560	0%	0%	0%
18	2018-09-01	600	565	+0.84%	+0.89%	+0.87%
19	2018-10-01	595	560	0%	0%	0%
20	2018-11-01	585	550	-1.68%	-1.79%	-1.73%
21	2018-12-01	575	540	-3.36%	-3.57%	-3.47%
22	2019-01-01	555	520	-6.72%	-7.14%	-6.93%
23	2019-02-01	545	510	-8.40%	-8.93%	-8.67%	price decrease
24	2019-03-01	545	510	0%	0%	0%
25	2019-04-01	535	500	-1.83%	-1.96%	-1.90%
26	2019-05-01	515	480	-5.50%	-5.88%	-5.69%
27	2019-06-01	515	480	-5.50%	-5.88%	-5.69%
28	2019-07-01	515	480	-5.50%	-5.88%	-5.69%
29	2019-08-01	505	470	-7.34%	-7.84%	-7.59%	price decrease
30	2019-09-01	505	470	0%	0%	0%
31	2019-10-01	485	450	-3.96%	-4.26%	-4.11%
32	2019-11-01	455	420	-9.90%	-10.64%	-10.27%	price decrease
33
MEPS

Cell Formulas
Range	Formula
D4:E4, D12:E12, D24:E24, D30:E30	D4	=(B4/B3)-100%
F4:F32	F4	=AVERAGE(D4:E4)
G4:G32	G4	=IF(F4>='Data for calculations'!$B$2,"price increase",IF(F4<='Data for calculations'!$A$2,"price decrease",""))
D5:E5, D13:E13, D25:E25, D31:E31	D5	=(B5/B3)-100%
D6:E6, D14:E14, D26:E26, D32:E32	D6	=(B6/B3)-100%
D7:E7, D15:E15, D27:E27	D7	=(B7/B3)-100%
D8:E8, D16:E16, D28:E28	D8	=(B8/B3)-100%
D9:E9, D17:E17, D29:E29	D9	=(B9/B3)-100%
D10:E10, D18:E18	D10	=(B10/B3)-100%
D11:E11, D19:E19	D11	=(B11/B3)-100%
D20:E20	D20	=(B20/B11)-100%
D21:E21	D21	=(B21/B11)-100%
D22:E22	D22	=(B22/B11)-100%
D23:E23	D23	=(B23/B11)-100%

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:G32	Expression	=ABS($F3)>=7%	text	NO

 

[Peter_SSs]

It is not entirely clear what your question is, but if you are looking for formulas for columns D:E then try this
Put something in G3 (I put "price")
Then these formulas in row 4 and copy down

Book1
	B	C	D	E		G
2	Highest	Lowest	Highest	Lowest
3	545	510				price
4	525	490	-3.67%	-3.92%
5	525	490	-3.67%	-3.92%
6	555	520	1.83%	1.96%
7	560	525	2.75%	2.94%
8	560	525	2.75%	2.94%
9	560	525	2.75%	2.94%
10	580	545	6.42%	6.86%
11	595	560	9.17%	9.80%		price increase
12	615	580	3.36%	3.57%
13	615	580	3.36%	3.57%
14	615	580	3.36%	3.57%
15	600	565	0.84%	0.89%
16	595	560	0.00%	0.00%
17	595	560	0.00%	0.00%
18	600	565	0.84%	0.89%
19	595	560	0.00%	0.00%
20	585	550	-1.68%	-1.79%
21	575	540	-3.36%	-3.57%
22	555	520	-6.72%	-7.14%
23	545	510	-8.40%	-8.93%		price decrease
24	545	510	0.00%	0.00%
25	535	500	-1.83%	-1.96%
26	515	480	-5.50%	-5.88%
27	515	480	-5.50%	-5.88%
28	515	480	-5.50%	-5.88%
29	505	470	-7.34%	-7.84%		price decrease
30	505	470	0.00%	0.00%
31	485	450	-3.96%	-4.26%
32	455	420	-9.90%	-10.64%		price decrease
MEPS

Cell Formulas
Range	Formula
D4:D32	D4	=B4/INDEX(B$3:B3,AGGREGATE(14,6,(ROW(B$3:B3)-ROW(B$3)+1)/(G$3:G3<>""),1))-1
E4:E32	E4	=C4/INDEX(C$3:C3,AGGREGATE(14,6,(ROW(C$3:C3)-ROW(C$3)+1)/(G$3:G3<>""),1))-1

 

[edvrdsdvskj]

Hi Peter,

Thank you for you help. It's the exactly that what I am looking for.
Your solution works perfectly, but in original data set comments in column G is generated by formula based on the values in column C. It means part of formula (G$3:G3<>"") can't be used.

Original data set is only in columns A and B. In column C I need a formula which dynamically chose the right values from column B.

Book1
	A	B	C	D	E	F
1	Period	HRC	±∆%		Formula in C	My comment
2	2017-06-01	545				no formula in C2 because it was starting point of this tracking
3	2017-07-01	525	-3.67%		=(B3/B2)-100%	B2, because ∆ is not less/greater or equal to ±7%
4	2017-08-01	525	-3.67%		=(B4/B2)-100%
5	2017-09-01	555	1.83%		=(B5/B2)-100%
6	2017-10-01	560	2.75%		=(B6/B2)-100%
7	2017-11-01	560	2.75%		=(B7/B2)-100%
8	2017-12-01	560	2.75%		=(B8/B2)-100%
9	2018-01-01	580	6.42%		=(B9/B2)-100%
10	2018-02-01	595	9.17%		=(B10/B2)-100%
11	2018-03-01	615	3.36%		=(B11/B10)-100%	B10, because ∆ in last month was greater or equal to 7%
12	2018-04-01	615	3.36%		=(B12/B10)-100%
13	2018-05-01	615	3.36%		=(B13/B10)-100%
14	2018-06-01	600	0.84%		=(B14/B10)-100%
15	2018-07-01	595	0.00%		=(B15/B10)-100%
16	2018-08-01	595	0.00%		=(B16/B10)-100%
17	2018-09-01	600	0.84%		=(B17/B10)-100%
18	2018-10-01	595	0.00%		=(B18/B10)-100%
19	2018-11-01	585	-1.68%		=(B19/B10)-100%
20	2018-12-01	575	-3.36%		=(B20/B10)-100%
21	2019-01-01	555	-6.72%		=(B21/B10)-100%
22	2019-02-01	545	-8.40%		=(B22/B10)-100%
23	2019-03-01	545	0.00%		=(B23/B22)-100%	B22, because ∆ in last month was less or equal to -7%
24	2019-04-01	535	-1.83%		=(B24/B22)-100%
25	2019-05-01	515	-5.50%		=(B25/B22)-100%
26	2019-06-01	515	-5.50%		=(B26/B22)-100%
27	2019-07-01	515	-5.50%		=(B27/B22)-100%
28	2019-08-01	505	-7.34%		=(B28/B22)-100%
29	2019-09-01	505	0.00%		=(B29/B28)-100%	B28, because ∆ in last month was less or equal to -7%
30	2019-10-01	485	-3.96%		=(B30/B28)-100%
31	2019-11-01	455	-9.90%		=(B31/B28)-100%
32	2019-12-01	500	9.89%		=(B32/B31)-100%	B31, because ∆ in last month was greater or equal to 7%
Sheet1

Cell Formulas
Range	Formula
C3, C11, C23, C29, C32	C3	=(B3/B2)-100%
C4, C12, C24, C30	C4	=(B4/B2)-100%
C5, C13, C25, C31	C5	=(B5/B2)-100%
C6, C14, C26	C6	=(B6/B2)-100%
C7, C15, C27	C7	=(B7/B2)-100%
C8, C16, C28	C8	=(B8/B2)-100%
C9, C17	C9	=(B9/B2)-100%
C10, C18	C10	=(B10/B2)-100%
C19	C19	=(B19/B10)-100%
C20	C20	=(B20/B10)-100%
C21	C21	=(B21/B10)-100%
C22	C22	=(B22/B10)-100%

 

[Peter_SSs]

but in original data set comments in column G is generated by formula based on the values in column C.

Ah, yes, I had overlooked that link.

Try this instead.

Book1
	B	C
1	HRC
2	545
3	525	-3.67%
4	525	-3.67%
5	555	1.83%
6	560	2.75%
7	560	2.75%
8	560	2.75%
9	580	6.42%
10	595	9.17%
11	615	3.36%
12	615	3.36%
13	615	3.36%
14	600	0.84%
15	595	0.00%
16	595	0.00%
17	600	0.84%
18	595	0.00%
19	585	-1.68%
20	575	-3.36%
21	555	-6.72%
22	545	-8.40%
23	545	0.00%
24	535	-1.83%
25	515	-5.50%
26	515	-5.50%
27	515	-5.50%
28	505	-7.34%
29	505	0.00%
30	485	-3.96%
31	455	-9.90%
32	500	9.89%
MEPS (2)

Cell Formulas
Range	Formula
C3:C32	C3	=B3/INDEX(B:B,IFERROR(AGGREGATE(14,6,ROW(B$2:B2)/(ABS(C$2:C2)>=0.07),1),ROW(B$2)))-1

 

[edvrdsdvskj]

Thank You Peter for Your help, works perfectly

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.