[Help] formula percentages

[vi28]

Dear gents,
I'm managing the supplies for my small food truck and I would appreciate some help with a formula.
I always require the same percentages / weight of supplies in kilos (20% flour, 45% rice and 35% spaghettis).
Each month I buy different amounts based on whatever is better priced, but I would like some help with a formula to figure out what I still need to buy to get my amounts even (column D)

Inventory	Target Ratio	Current (Kg)	Required shopping to reach target %
Flour	20%​	14.5​	?
Rice	45%​	33​	?
Spaghettis	35%​	25.5​	?
TOTAL		73​	?

 

[SanjayGMusafir]

Each month I buy different amounts based on whatever is better priced, but I would like some help with a formula to figure out what I still need to buy to get my amounts even

Information is too less to understand what you want in Column D.

 

[vi28]

Information is too less to understand what you want in Column D.

@SanjayGMusafir - I want to automate column D. Here's the fully manual process I use today as a fully manual process:
Step 1. After I buy another 5KG Flour and 10KG Rice I will have an inventory imbalance, where the biggest imbalance is of +6% flour
Step 2: So, I take the flour amount as my basis for my calculation. Now the 24.5 KG flour becomes my 20% and I use it to calculate what are the other 45% and 35% percentages based on it in column E
Step 3: I deduct the values in column E from what I have in Column B, so I know what amounts I still need to buy in column D. I would like to automate the whole process for column D

2022-09-26_Inventory_Calculation.xlsx
	A	B	C	D	E	F	G
1	Item	Target ratio	Current in KG	Required purchase in KG	Required for ratio in KG	Current ratio	Ratio inbalance
2	Flour	20.0%	24.5	0	24.5	26%	6%
3	Rice	45.0%	43.0	12	55.1	46%	1%
4	Spagettis	35.0%	25.5	17	42.9	27%	-8%
5	TOTAL		93.0
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=E2-C2
E2	E2	=C2
F2	F2	=C2/C5
G2:G4	G2	=F2-B2
E3	E3	=B3*C2/B2
F3	F3	=C3/C5
E4	E4	=B4*C2/B2
F4	F4	=C4/C5
C2	C2	=14.5+10
C3	C3	=33+10
C5	C5	=SUM(C2:C4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D4	Cell Value	>0	text	NO
G2:G4	Other Type	Color scale		NO

 

[SanjayGMusafir]

Understood. I also use same thing for my calculation. Shall revert back soon (in around 3 hrs) with an appropriate solution. I have to rush somewhere.

Thanks for your patience.

 

[SanjayGMusafir]

Check the solution below -

Things to note -

• Instead of 1 column E, I have used 2 column E & F
  • to avoid any circular reference or accidental overwriting the formula.
• Column I - I have used just to show you that the formula is calculating right.
• MOST IMPORTANT -
  • You MUST fill any one item in column F that you bought/buy first.
  • Rest it shall calculate in Column E
  • Posting more than 1 item in column F shall give you error.
• AND -
  • All three cells E2:E4 have different formula in them. Must remember that.

All Records.xlsb
	A	B	C	D	E	F	G	H	I
1	Item	Target ratio	Current (in KG)	Required purchase in KG	Required	Purchased (Fill any 1 you bought)	Current ratio	Ratio inbalance	Ratio Check (Just to Verify)
2	Flour	20%	24.50	(1.50)	23.00	23.00	26%	6%	20%
3	Rice	45%	43.00	8.75	51.75		46%	1%	45%
4	Spagettis	35%	25.50	14.75	40.25		27%	-8%	35%
5	TOTAL		93.00		115.00
Sheet1

Cell Formulas
Range	Formula
G2	G2	=C2/C5
H2:H4	H2	=G2-B2
I2:I4	I2	=E2/$E$5
G3	G3	=C3/C5
G4	G4	=C4/C5
C2	C2	=14.5+10
C3	C3	=33+10
D2:D4	D2	=IF(OR(E2=C2,E2=""),0,E2-C2)
C5,E5	C5	=SUM(C2:C4)
E2	E2	=IFS(COUNTBLANK($F$2:$F$4)=3,"",COUNTA(F2:F4)>1,"Fill any 1 only",F2<>"",F2,$F$3<>"",$F$3/$B$3*B2,TRUE,$F$4/$B$4*B2)
E3	E3	=IFS(COUNTBLANK($F$2:$F$4)=3,"",COUNTA(F2:F4)>1,"Fill any 1 only",F3<>"",F3,$F$2<>"",$F$2/$B$2*B3,TRUE,$F$4/$B$4*B3)
E4	E4	=IFS(COUNTBLANK($F$2:$F$4)=3,"",COUNTA(F2:F4)>1,"Fill any 1 only",F4<>"",F4,$F$3<>"",$F$3/$B$3*B4,TRUE,$F$2/$B$2*B4)