FORMULA - dynamic price change

edvrdsdvskj

New Member
Joined
Jun 3, 2018
Messages
7
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 3, 2018
Messages
7
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
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows
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
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,899
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,339
Messages
5,547,360
Members
410,789
Latest member
cpolek83
Top