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]

thanks for explianing @footoo

only thing is the 3% and 1.5% varies from one sheet to another so there is a cell where one can put in the %.

That's not a problem provided the rates are shown in the same cells on each sheet.

 

[shah0101]

shah101
I thought you were removing TRUNC from your formulas - it doesn't do what you need.

@footoo

I created three columns now, one with round difference is 0.09 one with trunc difference is 0.11 and one with simple formating to two decimal places difference is 0.10 // attaching sheet again for ease.

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	PART "A"			$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	PART "B"			$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	PART "C"			$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	PART "C"		Factory Trim Cost	$1.60	$1.60	$1.60
101						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Fabric Cost	$2.99	PART "A"		Fabric Cost	$4.97	$4.98	$4.98
102						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Accessories Cost	$1.28	PART "B"		Accessories Cost	$2.12	$2.13	$2.13
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.11	$12.10
108						$9.14 IS FIRST RESULT AND NEED TO GET TO $12.00				$9.14			FOB	$12.00	$12.00	$12.00
109
110									NEED TO GET TO $12.00:	$9.14
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	=ROUND((O$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
P101:P102	P101	=((P$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101)
N105	N105	=TRUNC(SUM(N100,N101,N102)*M105,2)
O105	O105	=ROUND(SUM(O100,O101,O102)*M105,2)
P105	P105	=(SUM(P100,P101,P102)*M105)
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

 

[CSmith]

This might help :

shah101 has sent xl2BB’s with his posts.
What he wants in N101:N108 is as follows :

N100 1.60 (fixed)
N101 Change the 2.99 in J101 to make the Total 12.00
N102 Change the 1.28 in J102 to make the Total 12.00
N103 2.96 (fixed)
N104 0.00 (fixed)
N105 Formula to calculate 3% of N100:N102 (fixed formula)
N106 Formula to calculate 1.5% of the Total, net of this amount (fixed formula)
N107 Total of N100:N106
N108 12 (the required Total)

Basically, he just needs to calculate the amounts for N101 and N102.

I’ve provided a macro that does what he wants, but he can’t use a macro.

Do the cells N101 and N102 need to be equal % adjustment? or ?
Is the $12 always 12? Why $12? at least from the example?

 

[footoo]

@footoo

I created three columns now, one with round difference is 0.09 one with trunc difference is 0.11 and one with simple formating to two decimal places difference is 0.10 // attaching sheet again for ease.

You don't have to do that.
All I can do is repeat (for about the fourth time) : format the cells as a number 0.00
Don't use TRUNC or ROUND in any of the formulas.

 

[footoo]

Do the cells N101 and N102 need to be equal % adjustment? or ?
Is the $12 always 12? Why $12? at least from the example?

Pro-rata based on J101 and J102.
The 12 per se is not important.

 

[CSmith]

Pro-rata based on J101 and J102.

It appears from the data that s/he is attempting to wrap their freight costs in with the materials . . . ?
Is this a standard amount or ?

The 12 per se is not important.

Hard to know how to get somewhere without knowing where you're going . . .
Just need to understand the problem to give a solution . . .

 

[footoo]

It appears from the data that s/he is attempting to wrap their freight costs in with the materials . . . ?
Is this a standard amount or ?


Hard to know how to get somewhere without knowing where you're going . . .
Just need to understand the problem to give a solution . . .

The problem is how to handle the formulas in N105 and N106 in calculating the changes to N101 and N102 so as to make the desired Total (whatever it happens to be).
It can be solved via Goal Seek but it involves a lot of sheets so not really practical.
I made use of Goal Seek in my macro.

 

[CSmith]

The problem is how to handle the formulas in N105 and N106 in calculating the changes to N101 and N102 so as to make the desired Total (whatever it happens to be).
It can be solved via Goal Seek but it involves a lot of sheets so not really practical.
I made use of Goal Seek in my macro.

I can give you the $12 easy knowing it is supposed to be $12 . . . If we don't know the # all I can give are inequality values (range of values where the system of equations would fit the $12), but still need to know . . . why this # to even begin ? Kind of like trying to developing a proof in mathematics without enough information . . . impossible

Why the result of $12?
Are the amounts to be adjusted in any ratio or? Need something besides arbitrary choices . . .

 

[CSmith]

I can give you the $12 easy knowing it is supposed to be $12 . . . If we don't know the # all I can give are inequality values (range of values where the system of equations would fit the $12), but still need to know . . . why this # to even begin ? Kind of like trying to developing a proof in mathematics without enough information . . . impossible

Why the result of $12?
Are the amounts to be adjusted in any ratio or? Need something besides arbitrary choices . . .

I've asked the same question many different ways without any real answer other than 'just cuz I picked it' . . . lol I'm not a mind reader I don't know what you will pick next . . .

 

[footoo]

The figure of 12 is not important and does not affect the method of calculation.