Complicated distribution amount into multiple cells in column based on cell with many steps

[tubrak]

Hi
here is my data in sheet

A.xlsm
	A	B	C	D	E	F	G		I	J	K	L	M	N
1	ITEM	DESCRIBE	QTY	PRICE	AMOUNT				DESCRIBE	AMOUNT		DATE	DESCRIBE	AMOUNTS
2	1	TTTM 1222 50PCS	10.00	12.00	120.00							01/01/2024	Tire installation	200
3	2	TTTM 1223 50PCS	7.00	16.00	112.00							01/01/2024	Tire patching inside	100
4	3	TTTM 1224 100PCS	4.00	11.00	44.00							02/01/2024	Tire lead	200
5	4	TTTM 1225 80PCS	5.00	6.00	30.00							02/01/2024	Tire installation	100
6	5	TTTM 1226 100PCS	10.00	10.00	100.00							03/01/2024	Tire patching	120
7	6	TTTM 1227 20 PCS	10.00	11.00	110.00							04/01/2024	Tire patching inside	100
8	7	TTTM 1228 25PCS	15.00	6.00	90.00							05/01/2024	Tire patching outside	120
9	8	TTTM 1229 44 PCS	25.00	5.00	125.00
10	9	TTTM 1230 35PCS	20.00	10.00	200.00
11	TOTAL			87.00	931.00
INVOICE

Cell Formulas
Range	Formula
E2:E10	E2	=C2*D2
D11:E11	D11	=SUM(D2:D10)

Cells with Data Validation
Cell	Allow	Criteria
I2,R1	List	=$R$1:$R$4

what I want just macro to do this
when select item by data validation in I2 then merge amounts in column N for duplicates items based on column M and show in J2
I will take value 12 in D2 as example how calculate
step 1=(12/87)=0.137931034
step2= 0.137931034*100=13.7931%
step3=13.7931*200/100=27.58621
step 4=27.58621+12=39.59
for more details
in step 1 value 12 is D2 , 87 will be in last cell in column D
step 3 200 will be in I2 ,as to 100 will be %
result will be in column D

A.xlsm
	A	B	C	D	E	F	G		I	J	K	L	M	N	O	P
1	ITEM	DESCRIBE	QTY	PRICE	AMOUNT				DESCRIBE	AMOUNT		DATE	DESCRIBE	AMOUNTS
2	1	TTTM 1222 50PCS	10.00	39.59	395.90				Tire patching inside	200		01/01/2024	Tire installation	200
3	2	TTTM 1223 50PCS	7.00	52.78	369.46							01/01/2024	Tire patching inside	300
4	3	TTTM 1224 100PCS	4.00	36.29	145.16							02/01/2024	Tire lead	200
5	4	TTTM 1225 80PCS	5.00	19.79	98.95							02/01/2024	Tire installation	100
6	5	TTTM 1226 100PCS	10.00	32.99	329.90							03/01/2024	Tire patching	120
7	6	TTTM 1227 20 PCS	10.00	36.29	362.90							04/01/2024	Tire patching inside	100
8	7	TTTM 1228 25PCS	15.00	19.79	296.85							05/01/2024	Tire patching outside	120
9	8	TTTM 1229 44 PCS	25.00	10.75	268.75
10	9	TTTM 1230 35PCS	20.00	32.99	659.80
11	TOTAL			281.26	2,927.67
INVOICE

Cell Formulas
Range	Formula
E2:E10	E2	=C2*D2
D11:E11	D11	=SUM(D2:D10)

 

[StephenCrump]

I can replicate your numbers (except your E9 which looks like an error) as follows:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ITEM	DESCRIBE	QTY	PRICE	Adj. Price	AMOUNT			DESCRIBE	AMOUNT		DATE	DESCRIBE	AMOUNTS
2	1	TTTM 1222 50PCS	10	12	39.59	395.90			Tire patching inside	200		1/01/2024	Tire installation	200
3	2	TTTM 1223 50PCS	7	16	52.78	369.46						1/01/2024	Tire patching inside	100
4	3	TTTM 1224 100PCS	4	11	36.29	145.16						2/01/2024	Tire lead	200
5	4	TTTM 1225 80PCS	5	6	19.79	98.95						2/01/2024	Tire installation	100
6	5	TTTM 1226 100PCS	10	10	32.99	329.90						3/01/2024	Tire patching	120
7	6	TTTM 1227 20 PCS	10	11	36.29	362.90						4/01/2024	Tire patching inside	100
8	7	TTTM 1228 25PCS	15	6	19.79	296.85						5/01/2024	Tire patching outside	120
9	8	TTTM 1229 44 PCS	25	5	16.49	412.25
10	9	TTTM 1230 35PCS	20	10	32.99	659.80
11	TOTAL			87		3,071.17
Sheet1

Cell Formulas
Range	Formula
J2	J2	=SUMIFS(N2:N8,M2:M8,I2)
E2:E10	E2	=ROUND(D2+D2/D$11*J$2,2)
D11,F11	D11	=SUM(D2:D10)
F2:F10	F2	=C2*E2

Two comments:

1. I'm not sure why you want a macro to do this. In my view, it's dangerous to have a macro overwriting the prices in column D, as you lose the ability to audit the calculation.

2. It's also not clear why you're spreading the $200 extra cost based on the prices per unit, i.e. ignoring the different quantities. Is the result correct, i.e. the increase in total price from $931.00 to $3,071.17?

 

[tubrak]

I can replicate your numbers (except your E9 which looks like an error) as follows:

sorry about error!

1. I'm not sure why you want a macro to do this. In my view, it's dangerous to have a macro overwriting the prices in column D, as you lose the ability to audit the calculation.

I realized that after posted my thread and waiting for comments from members what say.

It's also not clear why you're spreading the $200 extra cost based on the prices per unit

no this not cost , this is revenue I would show price sales for each item.

the increase in total price from $931.00 to $3,071.17?

will show me sales total
thank you so much for your help.

 

[StephenCrump]

OK, I'm glad it's giving you the results you want (although I have doubts about the validity of the maths).