Target increase/ decrease related issues.

[Masterrmind]

I have 600 retail where I have set targets for 4 categories. I have to increase some of the targets to ensure growth category-wise. I have to ensure the terms:
1. The amount increased in one or two or three components should be reduced proportionately according to the current target of the remaining components.
2. The Total Target amount should not be increased or decreased.
How can I do this with a formula? Can't be done manually as there are more than 600 retail.

As you can see, the amount of 20 increased for "target 2" for "A" and 20 decreased proportionately according to the current target of the remaining components. I did that manually. but I need a formula.

Pls help.
Thanks in advance.

 

[Cubist]

Here is a solution with D365. Maybe someone else can help you with Excel2021 from here without the use of FILTER(). You can round the number as desired.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Target1	Target2	Target3	Target4	TOTAL	Target1 Increase	Target2 Increase	Target3 Increase	Target4 Increase	Target1 New	Target2 New	Target3 New	Target4 New	TOTAL NEW
2	A	20	40	30	10	100		20			13.3333333	60	20	6.66666667	100
3	B	33	53	82	61	229	15				48	48.9438776	75.7244898	56.3316327	229
4	C	57	35	25	77	194	20	22			77	57	14.7058824	45.2941176	194
5	D	44	51	23	20	138			10		40.173913	46.5652174	33	18.2608696	138
6	E	92	55	59	59	265	12			20	104	39.5614035	42.4385965	79	265
Sheet2

Cell Formulas
Range	Formula
K1:N1	K1	=B1:E1&" New"
K2:N6	K2	=IF(G2=0,B2*(1-SUM($G2:$J2)/($F2-SUM(FILTER($B2:$E2,$G2:$J2>0)))),B2+G2)
O2:O6	O2	=SUM(K2:N2)
Dynamic array formulas.

 

[Cubist]

This should work with Excel2021 using SUMPRODUCT.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
16	Name	Target1	Target2	Target3	Target4	TOTAL	Target1 Increase	Target2 Increase	Target3 Increase	Target4 Increase	Target1 New	Target2 New	Target3 New	Target4 New	TOTAL NEW
17	A	20	40	30	10	100		20			13	60	20	7	100
18	B	33	53	82	61	229	15				48	49	76	56	229
19	C	57	35	25	77	194	20	22			77	57	15	45	194
20	D	44	51	23	20	138			10		40	47	33	18	138
21	E	92	55	59	59	265	12			20	104	40	42	79	265
Sheet2

Cell Formulas
Range	Formula
K16:N16	K16	=B16:E16&" New"
K17:N21	K17	=ROUND(IF(G17=0,B17*(1-SUM($G17:$J17)/($F17-SUMPRODUCT($B17:$E17,--($G17:$J17>0)))),B17+G17),0)
O17:O21	O17	=SUM(K17:N17)
Dynamic array formulas.

 

[Masterrmind]

This should work with Excel2021 using SUMPRODUCT.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
16	Name	Target1	Target2	Target3	Target4	TOTAL	Target1 Increase	Target2 Increase	Target3 Increase	Target4 Increase	Target1 New	Target2 New	Target3 New	Target4 New	TOTAL NEW
17	A	20	40	30	10	100		20			13	60	20	7	100
18	B	33	53	82	61	229	15				48	49	76	56	229
19	C	57	35	25	77	194	20	22			77	57	15	45	194
20	D	44	51	23	20	138			10		40	47	33	18	138
21	E	92	55	59	59	265	12			20	104	40	42	79	265
Sheet2

Cell Formulas
Range	Formula
K16:N16	K16	=B16:E16&" New"
K17:N21	K17	=ROUND(IF(G17=0,B17*(1-SUM($G17:$J17)/($F17-SUMPRODUCT($B17:$E17,--($G17:$J17>0)))),B17+G17),0)
O17:O21	O17	=SUM(K17:N17)
Dynamic array formulas.

Thank you....both formula works perfectly........