Price analysis

[s_balasubramanian]

Hai.

Im working in purchase department and analyzing Price/Rate of our purchased product as listed below. (image also attached)
Based on the data i want to know this prices are ""Increasing / Decreasing / Up & Down" (Status column)
Also i want to compare last two rate to find that are "Same / Increase / Decrease (Last 2 value status)

Refer last two column in image or Table the result which i want

I have this price data in MS office XL 2013

Please support to provide coding for this.

Thanks
S.Balasubramanian
TN, INDIA

	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2022	2022	Status	Last 2 value status
Material	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	trend
35000260	11023.2​			12000​			12920​	14875.5​				16831​	Increasing	increase
15000200	2​	3​	4​	5​	6​	7​	8​	9​	10​	12​		12​	Increasing	Same
50001294		900​			872​	872​		872​			870​		Decrease	Decrease
10001108	577.5​	577.5​		577.5​	637.5​								Increasing	increase
50001293		510​			618​			513​	560​			518​	up & down	Decrease

 

[s_balasubramanian]

xl excel help.xlsx
	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1					My questions									Solution I needed
2		2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2022	2022	Status	Last 2 value status
3	Material	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	trend
4	35000260	11023.2			12000			12920	14875.5				16831	Increasing	increase
5	15000200	2	3	4	5	6	7	8	9	10	12		12	Increasing	Same
6	50001294		900			872	872		872			870		Decrease	Decrease
7	10001108	577.5	577.5		577.5	637.5								Increasing	increase
8	50001293		510			618			513	560			518	up&down	Decrease
last NTH value

 

[BSALV]

last 2 values : 1st value/2nd value = 95-105% = same, >105% = increase, <95% = decrease
status trend : an UDF (with VBA)

Map2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1					My questions									Solution I needed
2		2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2022	2022	Status	Last 2 value status			0,00%	increase
3	Material	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	trend		joined		95,00%	same
4	35000260	11023,2			12000			12920	14875,5				16831	Increasing	increase	11023,2;12000;12920;14875,5;16831		105,00%	decrease
5	15000200	2	3	4	5	6	7	8	9	10	12		12	Increasing	same	2;3;4;5;6;7;8;9;10;12;12
6	50001294		900			872	872		872			870		Decrease	same	900;872;872;872;870
7	10001108	577,5	577,5		577,5	637,5								Increasing	increase	577,5;577,5;577,5;637,5
8	50001293		510			618			513	560			518	up&down	decrease	510;618;513;560;518
9
Blad3

Cell Formulas
Range	Formula
O4:O8	O4	=VLOOKUP(OFFSET($A4,,LARGE(COLUMN($B4:$M4)*(B4:M4<>""),2)-1,,)/OFFSET($A4,,LARGE(COLUMN($B4:$M4)*(B4:M4<>""),1)-1,,),$R$2:$S$4,2,1)
P4:P9	P4	=TEXTJOIN(";",1,B4:M4)

 

[BSALV]

just checking if a VBA-solution is allowed !

column O = monthly change in price for the last 2 values, for example row 4 = +13% between both values but 4 months interval = +13% / 4 = +3.1% / month
column N = see regression in P
for example row 4 = 10605,11 +541,63 * Month (R2=0,94) = meaning if you make a line through the data, month 0 = march 2021 has value 10.605 and every month 541 is added to that value.
R2 is a measure for fiability, .94 is very good. Because you add a value every month = increase.
Row 8 ---> R2 =0.02 = very low, you can't say anything about "up or down"

Sub linestt()
     arr = Range("B1:M9")
     For r = 4 To 8
          ptr = 0
          ReDim y(UBound(arr, 2))
          ReDim x(UBound(y))
          For j = 1 To UBound(arr, 2)
               If arr(r, j) <> "" Then
                    x(ptr) = j - 1                              'arr(9, j)
                    y(ptr) = arr(r, j)
                    ptr = ptr + 1
               End If
          Next
          ReDim Preserve y(ptr - 1)
          ReDim Preserve x(UBound(y))
          Range("N" & r).Resize(, 3).ClearContents

          If ptr >= 1 Then
               l2 = (y(UBound(y)) / y(UBound(y) - 1) - 1) / (x(UBound(x)) - x(UBound(x) - 1))

               a = WorksheetFunction.LinEst(y, x, , 1)
               Range("N" & r).Resize(, 3).Value = Array(IIf(a(3, 1) < 0.1, "up-down", IIf(a(1, 1) > 0, "Increase", "Decrease")), l2, Format(a(1, 2), "0.00") & IIf(a(1, 1) > 0, " +", " ") & Format(a(1, 1), "0.00") & " * Month (R2=" & Format(a(3, 1), "0.00\)"))
          End If
     Next
End Sub

priceanalysis.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					My questions									Solution I needed
2		2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2022	2022	Status	Last 2 value status
3	Material	maa	Apr	mei	Jun	Jul	Aug	Sep	okt	Nov	Dec	Jan	Feb	trend
4	35000260	11023,2			12000			12920	14875,5				16831	Increase	+3,3%	10605,11 +541,63 * Month (R2=0,94)
5	15000200	2	3	4	5	6	7	8	9	10	12		12	Increase	+9,1%	2,17 +0,95 * Month (R2=0,98)
6	50001294		900			872	872		872			870		Decrease	-0,1%	893,02 -2,93 * Month (R2=0,59)
7	10001108	577,5	577,5		577,5	637,5								Increase	+10,4%	568,50 +12,00 * Month (R2=0,53)
8	50001293		510			618			513	560			518	up-down	-2,5%	555,06 -1,82 * Month (R2=0,02)
9
10		0	1	2	3	4	5	6	7	8	9	10	11
Blad3

Cell Formulas
Range	Formula
B10:M10	B10	=COLUMN()-2

 

[s_balasubramanian]

Result of "Last 2 value status" is working
i am happy.

also your second suggestion will improve for better understanding.
i have to work it out.

Thanks for your update and support

last 2 values : 1st value/2nd value = 95-105% = same, >105% = increase, <95% = decrease
status trend : an UDF (with VBA)

Map2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1					My questions									Solution I needed
2		2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2022	2022	Status	Last 2 value status			0,00%	increase
3	Material	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	trend		joined		95,00%	same
4	35000260	11023,2			12000			12920	14875,5				16831	Increasing	increase	11023,2;12000;12920;14875,5;16831		105,00%	decrease
5	15000200	2	3	4	5	6	7	8	9	10	12		12	Increasing	same	2;3;4;5;6;7;8;9;10;12;12
6	50001294		900			872	872		872			870		Decrease	same	900;872;872;872;870
7	10001108	577,5	577,5		577,5	637,5								Increasing	increase	577,5;577,5;577,5;637,5
8	50001293		510			618			513	560			518	up&down	decrease	510;618;513;560;518
9
Blad3

Cell Formulas
Range	Formula
O4:O8	O4	=VLOOKUP(OFFSET($A4,,LARGE(COLUMN($B4:$M4)*(B4:M4<>""),2)-1,,)/OFFSET($A4,,LARGE(COLUMN($B4:$M4)*(B4:M4<>""),1)-1,,),$R$2:$S$4,2,1)
P4:P9	P4	=TEXTJOIN(";",1,B4:M4)

 

[BSALV]

i can give you a chart (still to be finetuned) where you can select a certain material in E18.
As you can see, it's difficult to translate a curve into terms like "increase, decrease, stable, up-down, ..."

Price Analysis

 

[s_balasubramanian]

im new earner in vba

i have opened one excel file (part1.xlsx) and copy the active cell content. (Vendorcode: 200050)

i want to filter the records in another file in part2.xlsx pertains to that vendor code 200050 (3rd column).

if part2.xlsx file is already opened in readonly, the msgbox1 is appear.

if i press yes copied value is not coming

if i press no the prog error is shown as msgbox2

Guide me how to proceed the next step of prog if file is already open

 

[Peter_SSs]

This is a completely different question so please start a new thread for it