# FORMULA - dynamic price change

#### edvrdsdvskj

##### New Member
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
ABCDEFG
1PeriodHot Rolled Coil±∆% ±∆% ±∆% Comment
2HighestLowestHighestLowestAVG
32017-06-01545510
42017-07-01525490-3.67%-3.92%-3.80%
52017-08-01525490-3.67%-3.92%-3.80%
62017-09-01555520+1.83%+1.96%+1.90%
72017-10-01560525+2.75%+2.94%+2.85%
82017-11-01560525+2.75%+2.94%+2.85%
92017-12-01560525+2.75%+2.94%+2.85%
102018-01-01580545+6.42%+6.86%+6.64%
112018-02-01595560+9.17%+9.80%+9.49%price increase
122018-03-01615580+3.36%+3.57%+3.47%
132018-04-01615580+3.36%+3.57%+3.47%
142018-05-01615580+3.36%+3.57%+3.47%
152018-06-01600565+0.84%+0.89%+0.87%
162018-07-015955600%0%0%
172018-08-015955600%0%0%
182018-09-01600565+0.84%+0.89%+0.87%
192018-10-015955600%0%0%
202018-11-01585550-1.68%-1.79%-1.73%
212018-12-01575540-3.36%-3.57%-3.47%
222019-01-01555520-6.72%-7.14%-6.93%
232019-02-01545510-8.40%-8.93%-8.67%price decrease
242019-03-015455100%0%0%
252019-04-01535500-1.83%-1.96%-1.90%
262019-05-01515480-5.50%-5.88%-5.69%
272019-06-01515480-5.50%-5.88%-5.69%
282019-07-01515480-5.50%-5.88%-5.69%
292019-08-01505470-7.34%-7.84%-7.59%price decrease
302019-09-015054700%0%0%
312019-10-01485450-3.96%-4.26%-4.11%
322019-11-01455420-9.90%-10.64%-10.27%price decrease
33
MEPS
Cell Formulas
RangeFormula
D4:E4, D12:E12, D24:E24, D30:E30D4=(B4/B3)-100%
F4:F32F4=AVERAGE(D4:E4)
G4:G32G4=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:E31D5=(B5/B3)-100%
D6:E6, D14:E14, D26:E26, D32:E32D6=(B6/B3)-100%
D7:E7, D15:E15, D27:E27D7=(B7/B3)-100%
D8:E8, D16:E16, D28:E28D8=(B8/B3)-100%
D9:E9, D17:E17, D29:E29D9=(B9/B3)-100%
D10:E10, D18:E18D10=(B10/B3)-100%
D11:E11, D19:E19D11=(B11/B3)-100%
D20:E20D20=(B20/B11)-100%
D21:E21D21=(B21/B11)-100%
D22:E22D22=(B22/B11)-100%
D23:E23D23=(B23/B11)-100%
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:G32Expression=ABS(\$F3)>=7%textNO

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Peter_SSs

##### MrExcel MVP, Moderator
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
BCDEG
2HighestLowestHighestLowest
3545510price
4525490-3.67%-3.92%
5525490-3.67%-3.92%
65555201.83%1.96%
75605252.75%2.94%
85605252.75%2.94%
95605252.75%2.94%
105805456.42%6.86%
115955609.17%9.80%price increase
126155803.36%3.57%
136155803.36%3.57%
146155803.36%3.57%
156005650.84%0.89%
165955600.00%0.00%
175955600.00%0.00%
186005650.84%0.89%
195955600.00%0.00%
20585550-1.68%-1.79%
21575540-3.36%-3.57%
22555520-6.72%-7.14%
23545510-8.40%-8.93%price decrease
245455100.00%0.00%
25535500-1.83%-1.96%
26515480-5.50%-5.88%
27515480-5.50%-5.88%
28515480-5.50%-5.88%
29505470-7.34%-7.84%price decrease
305054700.00%0.00%
31485450-3.96%-4.26%
32455420-9.90%-10.64%price decrease
MEPS
Cell Formulas
RangeFormula
D4:D32D4=B4/INDEX(B\$3:B3,AGGREGATE(14,6,(ROW(B\$3:B3)-ROW(B\$3)+1)/(G\$3:G3<>""),1))-1
E4:E32E4=C4/INDEX(C\$3:C3,AGGREGATE(14,6,(ROW(C\$3:C3)-ROW(C\$3)+1)/(G\$3:G3<>""),1))-1

#### edvrdsdvskj

##### New Member
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
ABCDEF
1PeriodHRC±∆% Formula in CMy comment
22017-06-01545no formula in C2 because it was starting point of this tracking
32017-07-01525-3.67%=(B3/B2)-100%B2, because ∆ is not less/greater or equal to ±7%
42017-08-01525-3.67%=(B4/B2)-100%
52017-09-015551.83%=(B5/B2)-100%
62017-10-015602.75%=(B6/B2)-100%
72017-11-015602.75%=(B7/B2)-100%
82017-12-015602.75%=(B8/B2)-100%
92018-01-015806.42%=(B9/B2)-100%
102018-02-015959.17%=(B10/B2)-100%
112018-03-016153.36%=(B11/B10)-100%B10, because ∆ in last month was greater or equal to 7%
122018-04-016153.36%=(B12/B10)-100%
132018-05-016153.36%=(B13/B10)-100%
142018-06-016000.84%=(B14/B10)-100%
152018-07-015950.00%=(B15/B10)-100%
162018-08-015950.00%=(B16/B10)-100%
172018-09-016000.84%=(B17/B10)-100%
182018-10-015950.00%=(B18/B10)-100%
192018-11-01585-1.68%=(B19/B10)-100%
202018-12-01575-3.36%=(B20/B10)-100%
212019-01-01555-6.72%=(B21/B10)-100%
222019-02-01545-8.40%=(B22/B10)-100%
232019-03-015450.00%=(B23/B22)-100%B22, because ∆ in last month was less or equal to -7%
242019-04-01535-1.83%=(B24/B22)-100%
252019-05-01515-5.50%=(B25/B22)-100%
262019-06-01515-5.50%=(B26/B22)-100%
272019-07-01515-5.50%=(B27/B22)-100%
282019-08-01505-7.34%=(B28/B22)-100%
292019-09-015050.00%=(B29/B28)-100%B28, because ∆ in last month was less or equal to -7%
302019-10-01485-3.96%=(B30/B28)-100%
312019-11-01455-9.90%=(B31/B28)-100%
322019-12-015009.89%=(B32/B31)-100%B31, because ∆ in last month was greater or equal to 7%
Sheet1
Cell Formulas
RangeFormula
C3, C11, C23, C29, C32C3=(B3/B2)-100%
C4, C12, C24, C30C4=(B4/B2)-100%
C5, C13, C25, C31C5=(B5/B2)-100%
C6, C14, C26C6=(B6/B2)-100%
C7, C15, C27C7=(B7/B2)-100%
C8, C16, C28C8=(B8/B2)-100%
C9, C17C9=(B9/B2)-100%
C10, C18C10=(B10/B2)-100%
C19C19=(B19/B10)-100%
C20C20=(B20/B10)-100%
C21C21=(B21/B10)-100%
C22C22=(B22/B10)-100%

#### Peter_SSs

##### MrExcel MVP, Moderator
but in original data set comments in column G is generated by formula based on the values in column C.

Book1
BC
1HRC
2545
3525-3.67%
4525-3.67%
55551.83%
65602.75%
75602.75%
85602.75%
95806.42%
105959.17%
116153.36%
126153.36%
136153.36%
146000.84%
155950.00%
165950.00%
176000.84%
185950.00%
19585-1.68%
20575-3.36%
21555-6.72%
22545-8.40%
235450.00%
24535-1.83%
25515-5.50%
26515-5.50%
27515-5.50%
28505-7.34%
295050.00%
30485-3.96%
31455-9.90%
325009.89%
MEPS (2)
Cell Formulas
RangeFormula
C3:C32C3=B3/INDEX(B:B,IFERROR(AGGREGATE(14,6,ROW(B\$2:B2)/(ABS(C\$2:C2)>=0.07),1),ROW(B\$2)))-1

#### edvrdsdvskj

##### New Member
Thank You Peter for Your help, works perfectly

#### Peter_SSs

##### MrExcel MVP, Moderator
You're welcome. Thanks for the follow-up.

Replies
9
Views
5K
Replies
1
Views
248
Replies
5
Views
286
Replies
8
Views
315
Replies
3
Views
434

1,114,528
Messages
5,548,570
Members
410,851
Latest member
glowe2020