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

 

[footoo]

Enter in N101 :
=(N$108-J$108)/(J$101+(J$102-J$102*0.03)*0.03+J$102)*J101+J101

Enter in N102 :
=(N$108-J$108)/(J$101+(J$102-J$102*0.03)*0.03+J$102)*J102+J102

Enter in N20 and drag down to N71 :
=IFERROR(IF(J20="","",J20*N$101/J$101), "")

 

[shah0101]

Enter in N101 :
=(N$108-J$108)/(J$101+(J$102-J$102*0.03)*0.03+J$102)*J101+J101

Enter in N102 :
=(N$108-J$108)/(J$101+(J$102-J$102*0.03)*0.03+J$102)*J102+J102

Enter in N20 and drag down to N71 :
=IFERROR(IF(J20="","",J20*N$101/J$101), "")

Thanks @footoo

1) N105 & N106 is still making trouble. Can you please help me on that please.

2) Can you please also guide / advise, since we need to keep everything to two decimal places what is the best formula to use? =trunc() or =round()?

3) Attaching sheet again after inserting above formulas. I just changed 0.03 and referenced it to cell where percentage is.

MR-EXCEL-001.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19								Unit	Price USD	Total Amount ( in USD )
20						THIS PART CAN BE CHANGED		MTR	$1.02	$0.95				$1.631525651645780
21						THIS PART CAN BE CHANGED
22						THIS PART CAN BE CHANGED
23						THIS PART CAN BE CHANGED		MTR	$0.31	$0.05				$0.085869771139252
24						THIS PART CAN BE CHANGED
25						THIS PART CAN BE CHANGED
26						THIS PART CAN BE CHANGED		MTR	$0.31	$0.05				$0.085869771139252
27						THIS PART CAN BE CHANGED
28						THIS PART CAN BE CHANGED
29						THIS PART CAN BE CHANGED		MTR	$2.49	$1.94				$3.331747120202970
30						THIS PART CAN BE CHANGED
31						THIS PART CAN BE CHANGED
32						THIS PART CAN BE CHANGED
33						THIS PART CAN BE CHANGED
34						THIS PART CAN BE CHANGED
35						THIS PART CAN BE CHANGED
36						THIS PART CAN BE CHANGED
37						THIS PART CAN BE CHANGED
38						THIS PART CAN BE CHANGED
39						THIS PART CAN BE CHANGED
40						THIS PART CAN BE CHANGED
41
42									Total Fabric Cost	$2.99				$5.135012314127260
43
44
45
46								Unit	Price USD	Total Amount ( in USD )
47						THIS PART CAN BE CHANGED		PCS	$0.41	$0.41				$0.704132123341865
48						THIS PART CAN BE CHANGED
49						THIS PART CAN BE CHANGED
50						THIS PART CAN BE CHANGED		PCS	$0.19	$0.38				$0.652610260658314
51						THIS PART CAN BE CHANGED
52						THIS PART CAN BE CHANGED
53						THIS PART CAN BE CHANGED		PCS	$0.23	$0.23				$0.395000947240558
54						THIS PART CAN BE CHANGED
55						THIS PART CAN BE CHANGED
56						THIS PART CAN BE CHANGED		PCS	$0.05	$0.05				$0.085869771139252
57						THIS PART CAN BE CHANGED		PCS	$0.02	$0.02				$0.034347908455701
58						THIS PART CAN BE CHANGED		PCS	$0.08	$0.08				$0.137391633822803
59						THIS PART CAN BE CHANGED		PCS	$0.09	$0.09				$0.154565588050653
60						THIS PART CAN BE CHANGED		PCS	$0.02	$0.02				$0.034347908455701
61						THIS PART CAN BE CHANGED
62						THIS PART CAN BE CHANGED
63						THIS PART CAN BE CHANGED
64						THIS PART CAN BE CHANGED
65						THIS PART CAN BE CHANGED
66						THIS PART CAN BE CHANGED
67						THIS PART CAN BE CHANGED
68						THIS PART CAN BE CHANGED
69						THIS PART CAN BE CHANGED
70
71									Total Trim Cost	$1.28				$2.198266141164850
72
73
74
75								UNIT	Unit Price ( in USD )	Total Amount ( in USD )
76						THIS PART CAN NOT BE CHANGED		CON	$0.65	$0.10				$0.100000000000000
77						THIS PART CAN NOT BE CHANGED		PCS	$0.13	$0.13				$0.130000000000000
78						THIS PART CAN NOT BE CHANGED		PCS	$0.10	$0.10				$0.100000000000000
79						THIS PART CAN NOT BE CHANGED		PCS	$0.02	$0.02				$0.020000000000000
80						THIS PART CAN NOT BE CHANGED		PCS	$0.04	$0.04				$0.040000000000000
81						THIS PART CAN NOT BE CHANGED		PCS	$0.02	$0.02				$0.020000000000000
82						THIS PART CAN NOT BE CHANGED		PCS	$0.07	$0.14				$0.140000000000000
83						THIS PART CAN NOT BE CHANGED		YDS	$0.65	$0.90				$0.900000000000000
84						THIS PART CAN NOT BE CHANGED		YDS	$0.11	$0.15				$0.150000000000000
85						THIS PART CAN NOT BE CHANGED
86						THIS PART CAN NOT BE CHANGED
87						THIS PART CAN NOT BE CHANGED
88						THIS PART CAN NOT BE CHANGED
89						THIS PART CAN NOT BE CHANGED
90						THIS PART CAN NOT BE CHANGED
91						THIS PART CAN NOT BE CHANGED
92						THIS PART CAN NOT BE CHANGED
93						THIS PART CAN NOT BE CHANGED
94						THIS PART CAN NOT BE CHANGED
95
96									Total Factory Trim Cost:	$1.60				$1.600000000000000
97
98
99								SUMMARY				SUMMARY
100						THIS IS TOTAL FROM ABOVE BLUE COLOR SECTION			Factory Trim Cost	$1.60			Factory Trim Cost	$1.600000000000000
101						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Fabric Cost	$2.99			Fabric Cost	$5.135012314127260
102						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Accessories Cost	$1.28			Accessories Cost	$2.198266141164850
103						THIS IS FIXED			CM Cost / Pcs	$2.96			CM Cost / Pcs	$2.960000000000000
104						THIS IS FIXED			Testing Cost / Pcs	$0.00			Testing Cost / Pcs	$0.000000000000000
105						TO BE CALCULATED ON ($1.6 + $2.99 + $1.28) X %		Commercial %	3.00%	$0.08		Commercial %	3.00%	$0.267998353658763
106						THIS IS FIXED BASED ON %		Commission %	1.50%	$0.00		Commission %	1.50%
107						THIS IS TOTAL FROM ABOVE			Total	$8.91			Total	$12.161276808950900
108						$8.91 IS FIRST RESULT AND NEED TO GET TO $12.00			FOB	$8.91			FOB	$12.000000000000000
Eastern

Cell Formulas
Range	Formula
N20:N40,N47:N69	N20	=IFERROR(IF(J20="","",J20*N$101/J$101), "")
N42	N42	=SUM(N19:N41)
N71	N71	=SUM(N46:N70)
N76:N94	N76	=+J76
N96	N96	=SUM(N75:N95)
N101:N102	N101	=(N$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101
N105	N105	=SUM(N100,N101,N102)*M105
J107,N107	J107	=SUM(J100:J106)

Cells with Data Validation
Cell	Allow	Criteria
H20:H40	List	=$BQ$1:$BQ$5
H47:H68	List	=$BQ$1:$BQ$5

 

[footoo]

In N105, the formula is :
=SUM(N100,N101,N102)*M105

In J105, the formula is :
=SUM(N101,N102)*M105

Which one is correct?

What problems are you having with N106?
How is it supposed to be calculated?

You can use the ROUND function to round to two decimals.
Alternatively, you can format the cells to display 2 decimals - in which case Excel retains the actual values in the cells

 

[shah0101]

In N105, the formula is :
=SUM(N100,N101,N102)*M105

In J105, the formula is :
=SUM(N101,N102)*M105

Which one is correct?

What problems are you having with N106?
How is it supposed to be calculated?

You can use the ROUND function to round to two decimals.
Alternatively, you can format the cells to display 2 decimals - in which case Excel retains the actual values in the cells

I can't thank you enough!

My bad, in J105 it should be =sum(N100,N101,N102)*I105

For N106, again need to calculate the commission based on its % of the total price $12.00

In N102 you'll notice the Accessories Cost is $2.20 (with the inserted formula) but if we cross check it with the total in N71 it is $2.19, how to fix that? I had similar problem with other files as well, always have a cent more or less when XL goes in decimal calculations. Please guide / advise.

Appending the file again:

MR-EXCEL-001.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19								Unit	Price USD	Total Amount ( in USD )
20						THIS PART CAN BE CHANGED		MTR	$1.02	$0.95				$1.600000000000000
21						THIS PART CAN BE CHANGED
22						THIS PART CAN BE CHANGED
23						THIS PART CAN BE CHANGED		MTR	$0.31	$0.05				$0.080000000000000
24						THIS PART CAN BE CHANGED
25						THIS PART CAN BE CHANGED
26						THIS PART CAN BE CHANGED		MTR	$0.31	$0.05				$0.080000000000000
27						THIS PART CAN BE CHANGED
28						THIS PART CAN BE CHANGED
29						THIS PART CAN BE CHANGED		MTR	$2.49	$1.94				$3.280000000000000
30						THIS PART CAN BE CHANGED
31						THIS PART CAN BE CHANGED
32						THIS PART CAN BE CHANGED
33						THIS PART CAN BE CHANGED
34						THIS PART CAN BE CHANGED
35						THIS PART CAN BE CHANGED
36						THIS PART CAN BE CHANGED
37						THIS PART CAN BE CHANGED
38						THIS PART CAN BE CHANGED
39						THIS PART CAN BE CHANGED
40						THIS PART CAN BE CHANGED
41
42									Total Fabric Cost	$2.99				$5.040000000000000
43
44
45
46								Unit	Price USD	Total Amount ( in USD )
47						THIS PART CAN BE CHANGED		PCS	$0.41	$0.41				$0.690000000000000
48						THIS PART CAN BE CHANGED
49						THIS PART CAN BE CHANGED
50						THIS PART CAN BE CHANGED		PCS	$0.19	$0.38				$0.640000000000000
51						THIS PART CAN BE CHANGED
52						THIS PART CAN BE CHANGED
53						THIS PART CAN BE CHANGED		PCS	$0.23	$0.23				$0.390000000000000
54						THIS PART CAN BE CHANGED
55						THIS PART CAN BE CHANGED
56						THIS PART CAN BE CHANGED		PCS	$0.05	$0.05				$0.080000000000000
57						THIS PART CAN BE CHANGED		PCS	$0.02	$0.02				$0.030000000000000
58						THIS PART CAN BE CHANGED		PCS	$0.08	$0.08				$0.140000000000000
59						THIS PART CAN BE CHANGED		PCS	$0.09	$0.09				$0.150000000000000
60						THIS PART CAN BE CHANGED		PCS	$0.02	$0.02				$0.030000000000000
61						THIS PART CAN BE CHANGED
62						THIS PART CAN BE CHANGED
63						THIS PART CAN BE CHANGED
64						THIS PART CAN BE CHANGED
65						THIS PART CAN BE CHANGED
66						THIS PART CAN BE CHANGED
67						THIS PART CAN BE CHANGED
68						THIS PART CAN BE CHANGED
69						THIS PART CAN BE CHANGED
70
71									Total Trim Cost	$1.28				$2.150000000000000
72
73
74
75								UNIT	Unit Price ( in USD )	Total Amount ( in USD )
76						THIS PART CAN NOT BE CHANGED		CON	$0.65	$0.10				$0.100000000000000
77						THIS PART CAN NOT BE CHANGED		PCS	$0.13	$0.13				$0.130000000000000
78						THIS PART CAN NOT BE CHANGED		PCS	$0.10	$0.10				$0.100000000000000
79						THIS PART CAN NOT BE CHANGED		PCS	$0.02	$0.02				$0.020000000000000
80						THIS PART CAN NOT BE CHANGED		PCS	$0.04	$0.04				$0.040000000000000
81						THIS PART CAN NOT BE CHANGED		PCS	$0.02	$0.02				$0.020000000000000
82						THIS PART CAN NOT BE CHANGED		PCS	$0.07	$0.14				$0.140000000000000
83						THIS PART CAN NOT BE CHANGED		YDS	$0.65	$0.90				$0.900000000000000
84						THIS PART CAN NOT BE CHANGED		YDS	$0.11	$0.15				$0.150000000000000
85						THIS PART CAN NOT BE CHANGED
86						THIS PART CAN NOT BE CHANGED
87						THIS PART CAN NOT BE CHANGED
88						THIS PART CAN NOT BE CHANGED
89						THIS PART CAN NOT BE CHANGED
90						THIS PART CAN NOT BE CHANGED
91						THIS PART CAN NOT BE CHANGED
92						THIS PART CAN NOT BE CHANGED
93						THIS PART CAN NOT BE CHANGED
94						THIS PART CAN NOT BE CHANGED
95
96									Total Factory Trim Cost:	$1.60				$1.600000000000000
97
98
99								SUMMARY				SUMMARY
100						THIS IS TOTAL FROM ABOVE BLUE COLOR SECTION			Factory Trim Cost	$1.60			Factory Trim Cost	$1.600000000000000
101						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Fabric Cost	$2.99			Fabric Cost	$5.050000000000000
102						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Accessories Cost	$1.28			Accessories Cost	$2.160000000000000
103						THIS IS FIXED			CM Cost / Pcs	$2.96			CM Cost / Pcs	$2.960000000000000
104						THIS IS FIXED			Testing Cost / Pcs	$0.00			Testing Cost / Pcs	$0.000000000000000
105						TO BE CALCULATED ON ($1.6 + $2.99 + $1.28) X %		Commercial %	3.00%	$0.20		Commercial %	3.00%	$0.260000000000000
106						THIS IS FIXED BASED ON %		Commission %	1.50%	$0.00		Commission %	1.50%
107						THIS IS TOTAL FROM ABOVE			Total	$9.03			Total	$12.030000000000000
108						$9.03 IS FIRST RESULT AND NEED TO GET TO $12.00			FOB	$9.03			FOB	$12.000000000000000
Eastern

Cell Formulas
Range	Formula
N20:N40,N47:N69	N20	=IFERROR(IF(J20="","",ROUND(J20*N$101/J$101,2)), "")
N42	N42	=SUM(N19:N41)
N71	N71	=SUM(N46:N70)
N76:N94	N76	=+J76
N96	N96	=SUM(N75:N95)
N101:N102	N101	=ROUND((N$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
J105	J105	=SUM(J100,J101,N102)*I105
N105	N105	=ROUND(SUM(N100,N101,N102)*M105,2)
J107,N107	J107	=SUM(J100:J106)

Cells with Data Validation
Cell	Allow	Criteria
H20:H40	List	=$BQ$1:$BQ$5
H47:H68	List	=$BQ$1:$BQ$5

 

[footoo]

For N106, again need to calculate the commission based on its % of the total price $12.00
So the 1.5% is on the total after charging the 1.5% ?
If so, the formula in J106 : =(J108-(J108*I106))*I106 and similarly for N106.
You cannot put a SUM function in J108 or N108 since it will result in a circular reference with J106 and N106.
Please note that the revised total for J107 is 9.14

Rather than using ROUND, it's better to format to 2 decimal places.
This should result in fewer rounding differences.
Also, don't put a SUM formula in N42 and N71. Just drag the formula in N20 through these cells.

 

[shah0101]

For N106, again need to calculate the commission based on its % of the total price $12.00
So the 1.5% is on the total after charging the 1.5% ?
If so, the formula in J106 : =(J108-(J108*I106))*I106 and similarly for N106.
You cannot put a SUM function in J108 or N108 since it will result in a circular reference with J106 and N106.
Please note that the revised total for J107 is 9.14

Rather than using ROUND, it's better to format to 2 decimal places.
This should result in fewer rounding differences.
Also, don't put a SUM formula in N42 and N71. Just drag the formula in N20 through these cells.

For N106, yes commission % of $12, inserted the formulas you suggested but the original price J107 now went to $9.17

okay, not using ROUND now, simply using =trunc() to see what that brings up.

okay, not putting in SUM formula using formula for N20.

Now the end result is still not we are trying to achieve which is $12.00, still have 0.07 difference!

for some reason I can not see the mini sheet upload button so attaching the image. Please advise.

Thanks again in advance!

 

[shah0101]

For N106, yes commission % of $12, inserted the formulas you suggested but the original price J107 now went to $9.17

okay, not using ROUND now, simply using =trunc() to see what that brings up.

okay, not putting in SUM formula using formula for N20.

Now the end result is still not we are trying to achieve which is $12.00, still have 0.07 difference!

for some reason I can not see the mini sheet upload button so attaching the image. Please advise.

Thanks again in advance!

@footoo

Found XL2BB:

MR-EXCEL-001.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19								Unit	Price USD	Total Amount ( in USD )
20						THIS PART CAN BE CHANGED		MTR	$1.02	$0.95				$1.57
21						THIS PART CAN BE CHANGED
22						THIS PART CAN BE CHANGED
23						THIS PART CAN BE CHANGED		MTR	$0.31	$0.05				$0.08
24						THIS PART CAN BE CHANGED
25						THIS PART CAN BE CHANGED
26						THIS PART CAN BE CHANGED		MTR	$0.31	$0.05				$0.08
27						THIS PART CAN BE CHANGED
28						THIS PART CAN BE CHANGED
29						THIS PART CAN BE CHANGED		MTR	$2.49	$1.94				$3.21
30						THIS PART CAN BE CHANGED
31						THIS PART CAN BE CHANGED
32						THIS PART CAN BE CHANGED
33						THIS PART CAN BE CHANGED
34						THIS PART CAN BE CHANGED
35						THIS PART CAN BE CHANGED
36						THIS PART CAN BE CHANGED
37						THIS PART CAN BE CHANGED
38						THIS PART CAN BE CHANGED
39						THIS PART CAN BE CHANGED
40						THIS PART CAN BE CHANGED
41
42									Total Fabric Cost	$2.99				$4.95
43
44
45
46								Unit	Price USD	Total Amount ( in USD )
47						THIS PART CAN BE CHANGED		PCS	$0.41	$0.41				$0.67
48						THIS PART CAN BE CHANGED
49						THIS PART CAN BE CHANGED
50						THIS PART CAN BE CHANGED		PCS	$0.19	$0.38				$0.62
51						THIS PART CAN BE CHANGED
52						THIS PART CAN BE CHANGED
53						THIS PART CAN BE CHANGED		PCS	$0.23	$0.23				$0.38
54						THIS PART CAN BE CHANGED
55						THIS PART CAN BE CHANGED
56						THIS PART CAN BE CHANGED		PCS	$0.05	$0.05				$0.08
57						THIS PART CAN BE CHANGED		PCS	$0.02	$0.02				$0.03
58						THIS PART CAN BE CHANGED		PCS	$0.08	$0.08				$0.13
59						THIS PART CAN BE CHANGED		PCS	$0.09	$0.09				$0.14
60						THIS PART CAN BE CHANGED		PCS	$0.02	$0.02				$0.03
61						THIS PART CAN BE CHANGED
62						THIS PART CAN BE CHANGED
63						THIS PART CAN BE CHANGED
64						THIS PART CAN BE CHANGED
65						THIS PART CAN BE CHANGED
66						THIS PART CAN BE CHANGED
67						THIS PART CAN BE CHANGED
68						THIS PART CAN BE CHANGED
69						THIS PART CAN BE CHANGED
70
71									Total Trim Cost	$1.28				$2.11
72
73
74
75								UNIT	Unit Price ( in USD )	Total Amount ( in USD )
76						THIS PART CAN NOT BE CHANGED		CON	$0.65	$0.10				$0.10
77						THIS PART CAN NOT BE CHANGED		PCS	$0.13	$0.13				$0.13
78						THIS PART CAN NOT BE CHANGED		PCS	$0.10	$0.10				$0.10
79						THIS PART CAN NOT BE CHANGED		PCS	$0.02	$0.02				$0.02
80						THIS PART CAN NOT BE CHANGED		PCS	$0.04	$0.04				$0.04
81						THIS PART CAN NOT BE CHANGED		PCS	$0.02	$0.02				$0.02
82						THIS PART CAN NOT BE CHANGED		PCS	$0.07	$0.14				$0.14
83						THIS PART CAN NOT BE CHANGED		YDS	$0.65	$0.90				$0.90
84						THIS PART CAN NOT BE CHANGED		YDS	$0.11	$0.15				$0.15
85						THIS PART CAN NOT BE CHANGED
86						THIS PART CAN NOT BE CHANGED
87						THIS PART CAN NOT BE CHANGED
88						THIS PART CAN NOT BE CHANGED
89						THIS PART CAN NOT BE CHANGED
90						THIS PART CAN NOT BE CHANGED
91						THIS PART CAN NOT BE CHANGED
92						THIS PART CAN NOT BE CHANGED
93						THIS PART CAN NOT BE CHANGED
94						THIS PART CAN NOT BE CHANGED
95
96									Total Factory Trim Cost:	$1.60				$1.60
97
98
99								SUMMARY				SUMMARY
100						THIS IS TOTAL FROM ABOVE BLUE COLOR SECTION			Factory Trim Cost	$1.60			Factory Trim Cost	$1.60
101						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Fabric Cost	$2.99			Fabric Cost	$4.95
102						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Accessories Cost	$1.28			Accessories Cost	$2.12
103						THIS IS FIXED			CM Cost / Pcs	$2.96			CM Cost / Pcs	$2.96
104						THIS IS FIXED			Testing Cost / Pcs	$0.00			Testing Cost / Pcs	$0.00
105						TO BE CALCULATED ON ($1.6 + $2.99 + $1.28) X %		Commercial %	3.00%	$0.20		Commercial %	3.00%	$0.26
106						THIS IS FIXED BASED ON %		Commission %	1.50%	$0.14		Commission %	1.50%	$0.18
107						THIS IS TOTAL FROM ABOVE			Total	$9.17			Total	$12.07
108						$9.17 IS FIRST RESULT AND NEED TO GET TO $12.00			FOB	$9.17			FOB	$12.00
Eastern

Cell Formulas
Range	Formula
N20:N40,N71,N47:N69,N42	N20	=IFERROR(IF(J20="","",TRUNC(J20*N$101/J$101,2)), "")
N76:N94,N96	N76	=+J76
N101:N102	N101	=TRUNC((N$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
J105	J105	=SUM(J100,J101,N102)*I105
J106,N106	J106	=(J108-(J108*I106))*I106
J107,N107	J107	=SUM(J100:J106)
N105	N105	=TRUNC(SUM(N100,N101,N102)*M105,2)

Cells with Data Validation
Cell	Allow	Criteria
H20:H40	List	=$BQ$1:$BQ$5
H47:H68	List	=$BQ$1:$BQ$5

 

[footoo]

The formula in J105 should be : =SUM(J100,J101,J102)*I105
The result in J105 is 0.18 (.1761) and the total 9.14

Don't use TRUNC. It will make the differences worse. Format the cells to 2 decimals.

You must adjust the formula in N106 for the appropriate columns.

The formulas in N101 and N102 need to be adjusted to account for the 1.5% amount.
Will look at it later.

 

[shah0101]

Thanks @footoo

The formula in J105 should be : =SUM(J100,J101,J102)*I105 (fixed)
The result in J105 is 0.18 (.1761) and the total 9.14 (fixed)

Don't use TRUNC. It will make the differences worse. Format the cells to 2 decimals. (noted, will try it three ways in format to 2 decimals, round to 2 decimals and trunc to 2 decmials in three columns side by side to see what effect each brings )

You must adjust the formula in N106 for the appropriate columns. (fixed)

The formulas in N101 and N102 need to be adjusted to account for the 1.5% amount.
Will look at it later. (thanks, looking forward)

MR-EXCEL-001.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18														WITH TRUNC	WITH ROUND	WITH FORMAT
19								Unit	Price USD	Total Amount ( in USD )
20						THIS PART CAN BE CHANGED		MTR	$1.02	$0.95				$1.57	$1.58	$1.58
21						THIS PART CAN BE CHANGED
22						THIS PART CAN BE CHANGED
23						THIS PART CAN BE CHANGED		MTR	$0.31	$0.05				$0.08	$0.08	$0.08
24						THIS PART CAN BE CHANGED
25						THIS PART CAN BE CHANGED
26						THIS PART CAN BE CHANGED		MTR	$0.31	$0.05				$0.08	$0.08	$0.08
27						THIS PART CAN BE CHANGED
28						THIS PART CAN BE CHANGED
29						THIS PART CAN BE CHANGED		MTR	$2.49	$1.94				$3.22	$3.22	$3.22
30						THIS PART CAN BE CHANGED
31						THIS PART CAN BE CHANGED
32						THIS PART CAN BE CHANGED
33						THIS PART CAN BE CHANGED
34						THIS PART CAN BE CHANGED
35						THIS PART CAN BE CHANGED
36						THIS PART CAN BE CHANGED
37						THIS PART CAN BE CHANGED
38						THIS PART CAN BE CHANGED
39						THIS PART CAN BE CHANGED
40						THIS PART CAN BE CHANGED
41
42									Total Fabric Cost	$2.99				$4.97	$4.97	$4.97
43
44
45
46								Unit	Price USD	Total Amount ( in USD )
47						THIS PART CAN BE CHANGED		PCS	$0.41	$0.41				$0.68	$0.68	$0.68
48						THIS PART CAN BE CHANGED
49						THIS PART CAN BE CHANGED
50						THIS PART CAN BE CHANGED		PCS	$0.19	$0.38				$0.63	$0.63	$0.63
51						THIS PART CAN BE CHANGED
52						THIS PART CAN BE CHANGED
53						THIS PART CAN BE CHANGED		PCS	$0.23	$0.23				$0.38	$0.38	$0.38
54						THIS PART CAN BE CHANGED
55						THIS PART CAN BE CHANGED
56						THIS PART CAN BE CHANGED		PCS	$0.05	$0.05				$0.08	$0.08	$0.08
57						THIS PART CAN BE CHANGED		PCS	$0.02	$0.02				$0.03	$0.03	$0.03
58						THIS PART CAN BE CHANGED		PCS	$0.08	$0.08				$0.13	$0.13	$0.13
59						THIS PART CAN BE CHANGED		PCS	$0.09	$0.09				$0.14	$0.15	$0.15
60						THIS PART CAN BE CHANGED		PCS	$0.02	$0.02				$0.03	$0.03	$0.03
61						THIS PART CAN BE CHANGED
62						THIS PART CAN BE CHANGED
63						THIS PART CAN BE CHANGED
64						THIS PART CAN BE CHANGED
65						THIS PART CAN BE CHANGED
66						THIS PART CAN BE CHANGED
67						THIS PART CAN BE CHANGED
68						THIS PART CAN BE CHANGED
69						THIS PART CAN BE CHANGED
70
71									Total Trim Cost	$1.28				$2.12	$2.13	$2.13
72
73
74
75								UNIT	Unit Price ( in USD )	Total Amount ( in USD )
76						THIS PART CAN NOT BE CHANGED		CON	$0.65	$0.10				$0.10	$0.10	$0.10
77						THIS PART CAN NOT BE CHANGED		PCS	$0.13	$0.13				$0.13	$0.13	$0.13
78						THIS PART CAN NOT BE CHANGED		PCS	$0.10	$0.10				$0.10	$0.10	$0.10
79						THIS PART CAN NOT BE CHANGED		PCS	$0.02	$0.02				$0.02	$0.02	$0.02
80						THIS PART CAN NOT BE CHANGED		PCS	$0.04	$0.04				$0.04	$0.04	$0.04
81						THIS PART CAN NOT BE CHANGED		PCS	$0.02	$0.02				$0.02	$0.02	$0.02
82						THIS PART CAN NOT BE CHANGED		PCS	$0.07	$0.14				$0.14	$0.14	$0.14
83						THIS PART CAN NOT BE CHANGED		YDS	$0.65	$0.90				$0.90	$0.90	$0.90
84						THIS PART CAN NOT BE CHANGED		YDS	$0.11	$0.15				$0.15	$0.15	$0.15
85						THIS PART CAN NOT BE CHANGED
86						THIS PART CAN NOT BE CHANGED
87						THIS PART CAN NOT BE CHANGED
88						THIS PART CAN NOT BE CHANGED
89						THIS PART CAN NOT BE CHANGED
90						THIS PART CAN NOT BE CHANGED
91						THIS PART CAN NOT BE CHANGED
92						THIS PART CAN NOT BE CHANGED
93						THIS PART CAN NOT BE CHANGED
94						THIS PART CAN NOT BE CHANGED
95
96									Total Factory Trim Cost:	$1.60				$1.60	$1.60	$1.60
97
98
99								SUMMARY				SUMMARY
100						THIS IS TOTAL FROM ABOVE BLUE COLOR SECTION			Factory Trim Cost	$1.60			Factory Trim Cost	$1.60	$1.60	$1.60
101						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Fabric Cost	$2.99			Fabric Cost	$4.97	$4.97	$4.97
102						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Accessories Cost	$1.28			Accessories Cost	$2.12	$2.12	$2.12
103						THIS IS FIXED			CM Cost / Pcs	$2.96			CM Cost / Pcs	$2.96	$2.96	$2.96
104						THIS IS FIXED			Testing Cost / Pcs	$0.00			Testing Cost / Pcs	$0.00	$0.00	$0.00
105						TO BE CALCULATED ON ($1.6 + $2.99 + $1.28) X %		Commercial %	3.00%	$0.18		Commercial %	3.00%	$0.26	$0.26	$0.26
106						THIS IS FIXED BASED ON %		Commission %	1.50%	$0.14		Commission %	1.50%	$0.18	$0.18	$0.18
107						THIS IS TOTAL FROM ABOVE			Total	$9.14			Total	$12.09	$12.09	$12.09
108						$9.14 IS FIRST RESULT AND NEED TO GET TO $12.00			FOB	$9.14			FOB	$12.00	$12.00	$12.00
Eastern

Cell Formulas
Range	Formula
N20:N40,N71,N47:N69,N42	N20	=IFERROR(IF(J20="","",TRUNC(J20*N$101/J$101,2)), "")
O20:O40,O71,O47:O69,O42	O20	=IFERROR(IF(J20="","",ROUND(J20*N$101/J$101,2)), "")
P20:P40,P71,P47:P69,P42	P20	=IFERROR(IF(J20="","",J20*N$101/J$101), "")
N76:N94,N96	N76	=+J76
O76:O94,O96	O76	=+J76
P76:P94,P96	P76	=+J76
N100:P100	N100	=+N96
N101:N102	N101	=TRUNC((N$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
O101:O102	O101	=TRUNC((O$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
P101:P102	P101	=TRUNC((P$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
N105	N105	=TRUNC(SUM(N100,N101,N102)*M105,2)
O105	O105	=TRUNC(SUM(O100,O101,O102)*M105,2)
P105	P105	=TRUNC(SUM(P100,P101,P102)*M105,2)
N106,J106	N106	=(N108-(N108*M106))*M106
O106	O106	=(O108-(O108*M106))*M106
P106	P106	=(P108-(P108*M106))*M106
N107:P107,J107	N107	=SUM(N100:N106)
J105	J105	=SUM(J100,J101,J102)*I105

Cells with Data Validation
Cell	Allow	Criteria
H20:H40	List	=$BQ$1:$BQ$5
H47:H68	List	=$BQ$1:$BQ$5

 

[footoo]

"Don't use TRUNC. It will make the differences worse. Format the cells to 2 decimals. (noted, will try it three ways in format to 2 decimals, round to 2 decimals and trunc to 2 decmials in three columns side by side to see what effect each brings )"
No. Just format the cells as number with 2 decimal places 0.00
(Right click>Format Cells>Number tab>Number>Decimal places: 2>OK

I couldn't work out formulas that avoid circular references (maybe someone else can), so below is a macro.
Before running the macro, do the following :
• Put 2.99 in N101 (no formula, but must not be blank)
• Put 1.28 in N102 (no formula, but must not be blank)
• In N105 put this formula : =SUM(N100:N104)*M105
• In N106 put this formula : =(SUM(N100:N105)+(SUM(N100:N105)*M106))*M106
• In N107 put this formula : =SUM(N100:N106)
• Put 12 in N108 (no formula) - you can put any total you want

After that, run the macro :

Sub Calculate_N101_N102()
Dim totalCost!: totalCost = [N108]
Dim x!, y!
[N107].GoalSeek Goal:=totalCost, ChangingCell:=[N101]
x = [N101]
y = [N102]
[N101] = (x + y) / ([J101] + [J102]) * [J101]
[N102] = (x + y) / ([J101] + [J102]) * [J102]
End Sub