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

 

[shah0101]

"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

thanks @footoo

noted, will format it to two decimal places.

VBA scripts and Macros are not an option. But thanks anyway.

There is one aggregate function could that help in anyway? or any other way around it?

@kvsrinivasamurthy @CSmith Would you be kind enough to look into the problem and help to solve please?

Thanks.

 

[footoo]

Other than a formula, could do with Goal Seek, but presumably you do not want to do manually.

 

[shah0101]

Other than a formula, could do with Goal Seek, but presumably you do not want to do manually.

@footoo Yes, please, unfortunately can not use VBA or Macros Please let me know if you could think of any other in built function please. Thanks again!

@kvsrinivasamurthy @CSmith Can you help and think of any solution please?

Thanks in advance!

 

[footoo]

Using Goal Seek will be quite messy in that a helper column will be required and if you want to remove it later, the formulas in N101 & N102 would first have to be converted to values only.

 

[shah0101]

Using Goal Seek will be quite messy in that a helper column will be required and if you want to remove it later, the formulas in N101 & N102 would first have to be converted to values on

If we need to convert the formulas to values then the whole purpose of automating the process will die

 

[CSmith]

thanks @footoo

noted, will format it to two decimal places.

VBA scripts and Macros are not an option. But thanks anyway.

There is one aggregate function could that help in anyway? or any other way around it?

@kvsrinivasamurthy @CSmith Would you be kind enough to look into the problem and help to solve please?

Thanks.

First let's start with how do you know it needs to be $12?
Something is missing from the details, but are you looking to generate the $12 or the numbers $5.16 and $2.21?
It looks like based on the sample data you provided the COMMISSION is 0%? But in other text you as for COMMISSION AND COMMERCIAL % to both be 3%?

3% commercial cost and commission

Without better idea of what direction you are "needing" to go I cannot provide a solution that fits your "needs". All I can do is throw out suggestions.

Perhaps you might:

• Stated your business case at the top and how you might adjust the values? Should they be linked adjustments within a given % of deviation?
• Stated what you’ve tried . . . There is a disconnect of your data from your description and it is difficult to get a clear picture of an answer without concise delimiters of how things are/can be adjusted.
• Explained what should have happened and why it should be that way . . . Your data shows you trying to get to $12 but why/how did you get that number?
• A xl2BB would have made this the perfect post!

 

[shah0101]

First let's start with how do you know it needs to be $12?
Something is missing from the details, but are you looking to generate the $12 or the numbers $5.16 and $2.21?
It looks like based on the sample data you provided the COMMISSION is 0%? But in other text you as for COMMISSION AND COMMERCIAL % to both be 3%?


Without better idea of what direction you are "needing" to go I cannot provide a solution that fits your "needs". All I can do is throw out suggestions.

Perhaps you might:

• Stated your business case at the top and how you might adjust the values? Should they be linked adjustments within a given % of deviation?
• Stated what you’ve tried . . . There is a disconnect of your data from your description and it is difficult to get a clear picture of an answer without concise delimiters of how things are/can be adjusted.
• Explained what should have happened and why it should be that way . . . Your data shows you trying to get to $12 but why/how did you get that number?
• A xl2BB would have made this the perfect post!

Thanks @CSmith

I'll try to explain as much as possible in red.

First let's start with how do you know it needs to be $12? ....We get this number by word of mouth that we need to bring that calculation to this number, no logic, no reference to the is number and it varies from one cost sheet to another......
Something is missing from the details, but are you looking to generate the $12 or the numbers $5.16 and $2.21? ....$12 is fixed not generated, we need to adjust above prices to bring that number from original to new price......
It looks like based on the sample data you provided the COMMISSION is 0%? But in other text you as for COMMISSION AND COMMERCIAL % to both be 3%? ....yes, initially I tried with commercial only (which is calculted on sum of three cells j100+j101+j102) and its not 3% fixed, % varies from one sheet to another. For commission, it should be calculated on $12 which will then go into $12 itself with other numbers. commission is also not fixed, it also varies from one sheet to another.

3% commercial cost and commission

Without better idea of what direction you are "needing" to go I cannot provide a solution that fits your "needs". All I can do is throw out suggestions.
....I am all here and I can explain everything again if have any questions... basic idea is to get $9.14 to $12.00 in with making adjustment in: (1) total of part "A", j101 (2) total part "B" j102, (3) j105 (4) j106
Perhaps you might:

• Stated your business case at the top and how you might adjust the values? Should they be linked adjustments within a given % of deviation? .....yes should change with % deviation....
• Stated what you’ve tried . . . There is a disconnect of your data from your description and it is difficult to get a clear picture of an answer without concise delimiters of how things are/can be adjusted. ....tried again above in red......
• Explained what should have happened and why it should be that way . . . Your data shows you trying to get to $12 but why/how did you get that number? .....explained as above.....
• A xl2BB would have made this the perfect post! .....sure, attaching the same again.....

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.97	$4.97
102						THIS IS TOTAL FROM ABOVE GREEN COLOR SECTION			Accessories Cost	$1.28	PART "B"		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				$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	=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]

First let's start with how do you know it needs to be $12?
Something is missing from the details, but are you looking to generate the $12 or the numbers $5.16 and $2.21?
It looks like based on the sample data you provided the COMMISSION is 0%? But in other text you as for COMMISSION AND COMMERCIAL % to both be 3%?


Without better idea of what direction you are "needing" to go I cannot provide a solution that fits your "needs". All I can do is throw out suggestions.

Perhaps you might:

• Stated your business case at the top and how you might adjust the values? Should they be linked adjustments within a given % of deviation?
• Stated what you’ve tried . . . There is a disconnect of your data from your description and it is difficult to get a clear picture of an answer without concise delimiters of how things are/can be adjusted.
• Explained what should have happened and why it should be that way . . . Your data shows you trying to get to $12 but why/how did you get that number?
• A xl2BB would have made this the perfect post!

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.

 

[shah0101]

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.

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 %.

 

[footoo]

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