Calculating a Number from the very number it is based on

[shah0101]

Hello Experts,

I have a problem and need your help please. I need to bring total price of 8.91 to 12.00 but the problem is with 3% commercial cost and commission because it is calculated on the very same value which needs to be adjusted.

Is there any formula or similar to get around this please?

Below is just a summary for ease of understand. Actually ton of background calculations are involved in multiple sheets.

Thanks in Advance.

		BELOW IS FIXED	TWO ITEMS CAN CHANGE
	Trim Cost (A)	$1.60	$1.60	CAN NOT CHANGE
	Fabric Cost	$2.99	$5.16	CAN ADJUST
	Accessories Cost (B)	$1.28	$2.21	CAN ADJUST
	CM COST	$2.96	$2.99	CAN NOT CHANGE
Commercial %	3% OF A+B	$0.08	$0.08	CAN NOT CHANGE
	Total	$8.91	$12.04	CAN NOT CHANGE
Commission %	0	$0.00	$0.00	CAN NOT CHANGE
	TOTAL PRICE	$8.91	$12.00	THIS IS TOTAL

 

[CSmith]

Quick Point about this . . . Not sure about the commercial % amt as you are trying to calc from the changed fields I went backwards mostly and used across the total of everything, but if I don't do it that way it gets more complicated . . .

 

[CSmith]

This should be it IF you only want the 3% on the remainder (should be close)

Just change the Desired FOB and it should auto populate the rest . . .

Book1
	H	I	J	K	L	M	N
99	SUMMARY				SUMMARY
100		Factory Trim Cost	1.6	PART "C"		Factory Trim Cost	$1.60
101		Fabric Cost	2.99	PART "A"		Fabric Cost	$5.57
102		Accessories Cost	1.28	PART "B"		Accessories Cost	$2.39
103		CM Cost / Pcs	2.96			CM Cost / Pcs	$2.96
104		Testing Cost / Pcs	0			Testing Cost / Pcs	$0.00
105	Commercial %	3.00%	0.1761		Commercial %	3.00%	$0.29
106	Commission %	1.50%	0.1350435		Commission %	1.50%	$0.19
107		Total	9.1411435			Total	$13.00
108			9.14			FOB	$13.00
109		Desired FOB $	13
Sheet2

Cell Formulas
Range	Formula
N100,N103	N100	=J100
N101	N101	=ROUND(J101*(DesiredFOB-SUM(N103:N106,N100))/SUM($J$101:$J$102),2)
N102	N102	=ROUND($J$102*(DesiredFOB-SUM(N103:N106,N100))/SUM($J$101:$J$102),2)
J105	J105	=SUM(J100,J101,J102)*I105
J106	J106	=(J108-(J108*I106))*I106
J107,N107	J107	=SUM(J100:J106)
N105	N105	=(DesiredFOB-SUM(N106,N104,N103))-((DesiredFOB-SUM(N106,N104,N103))/(1+$I105))
N106	N106	=DesiredFOB-(DesiredFOB/(1+$I106))
N108	N108	=SUM(N100:N106)

Named Ranges
Name	Refers To	Cells
DesiredFOB	=Sheet2!$J$109	N101:N102, N105:N106

 

[footoo]

...

 

[CSmith]

...

I mistakenly thought the commercial % was for the whole amount of all the rest of the values so I updated the formula for it . . . and removed his other columns.

 

[shah0101]

I mistakenly thought the commercial % was for the whole amount of all the rest of the values so I updated the formula for it . . . and removed his other columns.

Thank you @CSmith & @footoo // This is too much to take in for a novice like me. I will check and let you know tomorrow. Thanks again for your time and efforts.

 

[footoo]

Writing the macro was much easier than creating the formulas.

 

[footoo]

Thank you @CSmith & @footoo // This is too much to take in for a novice like me. I will check and let you know tomorrow. Thanks again for your time and efforts.

I'm not whether this was mentioned but you need to create a Name : DesiredFOB referring to J109.
Just noticed it was mentioned.

 

[shah0101]

@footoo @CSmith Thank you very much Everything is working okay now. Thank a ton again!