Calculating a Number from the very number it is based on

S

shah0101

Board Regular
Joined
Jul 4, 2019
Messages
118
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 FIXEDTWO ITEMS CAN CHANGE
Trim Cost (A)$1.60$1.60CAN NOT CHANGE
Fabric Cost$2.99$5.16CAN ADJUST
Accessories Cost (B)$1.28$2.21CAN ADJUST
CM COST$2.96$2.99CAN NOT CHANGE
Commercial %3% OF A+B$0.08$0.08CAN NOT CHANGE
Total$8.91$12.04CAN NOT CHANGE
Commission %0$0.00$0.00CAN NOT CHANGE
TOTAL PRICE$8.91$12.00THIS IS TOTAL
 
Last edited by a moderator:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Knowing the outcome of $12 and with 3% commission would mean a total NET total amount of 97.087% of the 12$ = 11.65 should be the net amount to get you to $12
Not sure what else you're really trying to do but simple is knowing the $12 final and commission of 3% being included just divide $12 by 1.03 to get 11.65 . . . which would give you the resultant 100% of the pre $12 price minus the 3% commission increase.
 
Upvote 0
CSmith said:
Knowing the outcome of $12 and with 3% commission would mean a total NET total amount of 97.087% of the 12$ = 11.65 should be the net amount to get you to $12
Not sure what else you're really trying to do but simple is knowing the $12 final and commission of 3% being included just divide $12 by 1.03 to get 11.65 . . . which would give you the resultant 100% of the pre $12 price minus the 3% commission increase.
Click to expand...
Dear CSmith,

Thanks for lending a helping hand.

Actually the target $12 where I am trying to reach is inclusive of 3% and that 3% is to be calculated on "A" & "B" and I can only adjust "B" and this "B" has a break up of number of items. I am not sure if I am clearly able to explain my problem.

Thanks again!
 
Last edited by a moderator:
Upvote 0
Well not sure what else you're needing . . . Is the 3% commission only on the $1.60 + $2.21 ? and this is the only variable ?
Best to create a table showing static and variable items. Where is the 3% commission amount tied to? What items are variable and how are they variable? If just a need to go backwards is pretty simple, but not if you do not have some limiting factor . . . otherwise you can have a range of values that fits your calculations . . . and not a simple static number or set of them.
 
Upvote 0
Assuming your columns are A:F and the headings are in row 1 :

• Enter in D3
=(D$9-C$9)/(C$3+(C$4-C$4*0.03))*0.03+C$4)*C3+C3
• Enter in D4
=(D$9-C$9)/(C$3+(C$4-C$4*0.03))*0.03+C$4)*C4+C4

The commission in column C should be 0.09 (not 0.08) and the total price should be 8.92
 
Last edited:
Upvote 0
CSmith said:
Well not sure what else you're needing . . . Is the 3% commission only on the $1.60 + $2.21 ? and this is the only variable ?
Best to create a table showing static and variable items. Where is the 3% commission amount tied to? What items are variable and how are they variable? If just a need to go backwards is pretty simple, but not if you do not have some limiting factor . . . otherwise you can have a range of values that fits your calculations . . . and not a simple static number or set of them.
Click to expand...
Thanks again for quick reply.

in first column the commission is on $1.60 & $1.28 are the two values which and total it makes is $8.91. Now since I have to increase $8.91 to $12.00 I have increased $1.28 to $2.21. This I did all manually and there are hundreds of sheets like that which I want to automate.

Was I able to explain myself this time?

Thanks in advance.
 
Upvote 0
My previous post had an extra bracket. Should be :

=(D$9-C$9)/(C$3+(C$4-C$4*0.03)*0.03+C$4)*C3+C3
=(D$9-C$9)/(C$3+(C$4-C$4*0.03)*0.03+C$4)*C4+C4
 
Upvote 0
Dear Footoo and CSmith,

Tried several ways (which I knew) but it is not working out.

Uploading the sheet. Basic idea is $8.91 is current price and need reverse engineering to get to $12.00. Please guide / advise.

Thanks in advance.


MR-EXCEL-001.xlsx
ABCDEFGHIJKLMN
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19Unit Price USDTotal Amount ( in USD )
20THIS PART CAN BE CHANGEDMTR$1.02$0.95
21THIS PART CAN BE CHANGED
22THIS PART CAN BE CHANGED
23THIS PART CAN BE CHANGEDMTR$0.31$0.05
24THIS PART CAN BE CHANGED
25THIS PART CAN BE CHANGED
26THIS PART CAN BE CHANGEDMTR$0.31$0.05
27THIS PART CAN BE CHANGED
28THIS PART CAN BE CHANGED
29THIS PART CAN BE CHANGEDMTR$2.49$1.94
30THIS PART CAN BE CHANGED
31THIS PART CAN BE CHANGED
32THIS PART CAN BE CHANGED
33THIS PART CAN BE CHANGED
34THIS PART CAN BE CHANGED
35THIS PART CAN BE CHANGED
36THIS PART CAN BE CHANGED
37THIS PART CAN BE CHANGED
38THIS PART CAN BE CHANGED
39THIS PART CAN BE CHANGED
40THIS PART CAN BE CHANGED
41
42Total Fabric Cost$2.99
43
44
45
46UnitPrice USDTotal Amount ( in USD )
47THIS PART CAN BE CHANGEDPCS$0.41$0.41
48THIS PART CAN BE CHANGED
49THIS PART CAN BE CHANGED
50THIS PART CAN BE CHANGEDPCS$0.19$0.38
51THIS PART CAN BE CHANGED
52THIS PART CAN BE CHANGED
53THIS PART CAN BE CHANGEDPCS$0.23$0.23
54THIS PART CAN BE CHANGED
55THIS PART CAN BE CHANGED
56THIS PART CAN BE CHANGEDPCS$0.05$0.05
57THIS PART CAN BE CHANGEDPCS$0.02$0.02
58THIS PART CAN BE CHANGEDPCS$0.08$0.08
59THIS PART CAN BE CHANGEDPCS$0.09$0.09
60THIS PART CAN BE CHANGEDPCS$0.02$0.02
61THIS PART CAN BE CHANGED
62THIS PART CAN BE CHANGED
63THIS PART CAN BE CHANGED
64THIS PART CAN BE CHANGED
65THIS PART CAN BE CHANGED
66THIS PART CAN BE CHANGED
67THIS PART CAN BE CHANGED
68THIS PART CAN BE CHANGED
69THIS PART CAN BE CHANGED
70
71Total Trim Cost$1.28
72
73
74
75UNITUnit Price ( in USD )Total Amount ( in USD )
76THIS PART CAN NOT BE CHANGEDCON$0.65$0.10
77THIS PART CAN NOT BE CHANGEDPCS$0.13$0.13
78THIS PART CAN NOT BE CHANGEDPCS$0.10$0.10
79THIS PART CAN NOT BE CHANGEDPCS$0.02$0.02
80THIS PART CAN NOT BE CHANGEDPCS$0.04$0.04
81THIS PART CAN NOT BE CHANGEDPCS$0.02$0.02
82THIS PART CAN NOT BE CHANGEDPCS$0.07$0.14
83THIS PART CAN NOT BE CHANGEDYDS$0.65$0.90
84THIS PART CAN NOT BE CHANGEDYDS$0.11$0.15
85THIS PART CAN NOT BE CHANGED
86THIS PART CAN NOT BE CHANGED
87THIS PART CAN NOT BE CHANGED
88THIS PART CAN NOT BE CHANGED
89THIS PART CAN NOT BE CHANGED
90THIS PART CAN NOT BE CHANGED
91THIS PART CAN NOT BE CHANGED
92THIS PART CAN NOT BE CHANGED
93THIS PART CAN NOT BE CHANGED
94THIS PART CAN NOT BE CHANGED
95
96Total Factory Trim Cost:$1.60
97
98
99SUMMARYSUMMARY
100THIS IS TOTAL FROM ABOVE BLUE COLOR SECTIONFactory Trim Cost$1.60Factory Trim Cost
101THIS IS TOTAL FROM ABOVE GREEN COLOR SECTIONFabric Cost$2.99Fabric Cost
102THIS IS TOTAL FROM ABOVE GREEN COLOR SECTIONAccessories Cost$1.28Accessories Cost
103THIS IS FIXEDCM Cost / Pcs$2.96CM Cost / Pcs
104THIS IS FIXEDTesting Cost / Pcs$0.00Testing Cost / Pcs
105TO BE CALCULATED ON ($1.6 + $2.99 + $) X % Commercial %3.00%$0.08Commercial %3.00%
106THIS IS FIXED BASED ON %Commission %0.00%$0.00Commission %0.00%
107THIS IS TOTAL FROM ABOVETotal$8.91Total$0.00
108$8.91 IS FIRST RESULT AND NEED TO GET TO $12.00FOB$8.91FOB$12.00
Eastern
Cell Formulas
RangeFormula
J107,N107J107=SUM(J100:J106)
Cells with Data Validation
CellAllowCriteria
H20:H40List=$BQ$1:$BQ$5
H47:H68List=$BQ$1:$BQ$5
 
Upvote 0
shah0101 said:
Dear Footoo and CSmith,

Tried several ways (which I knew) but it is not working out.

Uploading the sheet. Basic idea is $8.91 is current price and need reverse engineering to get to $12.00. Please guide / advise.

Thanks in advance.


MR-EXCEL-001.xlsx
ABCDEFGHIJKLMN
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19Unit Price USDTotal Amount ( in USD )
20THIS PART CAN BE CHANGEDMTR$1.02$0.95
21THIS PART CAN BE CHANGED
22THIS PART CAN BE CHANGED
23THIS PART CAN BE CHANGEDMTR$0.31$0.05
24THIS PART CAN BE CHANGED
25THIS PART CAN BE CHANGED
26THIS PART CAN BE CHANGEDMTR$0.31$0.05
27THIS PART CAN BE CHANGED
28THIS PART CAN BE CHANGED
29THIS PART CAN BE CHANGEDMTR$2.49$1.94
30THIS PART CAN BE CHANGED
31THIS PART CAN BE CHANGED
32THIS PART CAN BE CHANGED
33THIS PART CAN BE CHANGED
34THIS PART CAN BE CHANGED
35THIS PART CAN BE CHANGED
36THIS PART CAN BE CHANGED
37THIS PART CAN BE CHANGED
38THIS PART CAN BE CHANGED
39THIS PART CAN BE CHANGED
40THIS PART CAN BE CHANGED
41
42Total Fabric Cost$2.99
43
44
45
46UnitPrice USDTotal Amount ( in USD )
47THIS PART CAN BE CHANGEDPCS$0.41$0.41
48THIS PART CAN BE CHANGED
49THIS PART CAN BE CHANGED
50THIS PART CAN BE CHANGEDPCS$0.19$0.38
51THIS PART CAN BE CHANGED
52THIS PART CAN BE CHANGED
53THIS PART CAN BE CHANGEDPCS$0.23$0.23
54THIS PART CAN BE CHANGED
55THIS PART CAN BE CHANGED
56THIS PART CAN BE CHANGEDPCS$0.05$0.05
57THIS PART CAN BE CHANGEDPCS$0.02$0.02
58THIS PART CAN BE CHANGEDPCS$0.08$0.08
59THIS PART CAN BE CHANGEDPCS$0.09$0.09
60THIS PART CAN BE CHANGEDPCS$0.02$0.02
61THIS PART CAN BE CHANGED
62THIS PART CAN BE CHANGED
63THIS PART CAN BE CHANGED
64THIS PART CAN BE CHANGED
65THIS PART CAN BE CHANGED
66THIS PART CAN BE CHANGED
67THIS PART CAN BE CHANGED
68THIS PART CAN BE CHANGED
69THIS PART CAN BE CHANGED
70
71Total Trim Cost$1.28
72
73
74
75UNITUnit Price ( in USD )Total Amount ( in USD )
76THIS PART CAN NOT BE CHANGEDCON$0.65$0.10
77THIS PART CAN NOT BE CHANGEDPCS$0.13$0.13
78THIS PART CAN NOT BE CHANGEDPCS$0.10$0.10
79THIS PART CAN NOT BE CHANGEDPCS$0.02$0.02
80THIS PART CAN NOT BE CHANGEDPCS$0.04$0.04
81THIS PART CAN NOT BE CHANGEDPCS$0.02$0.02
82THIS PART CAN NOT BE CHANGEDPCS$0.07$0.14
83THIS PART CAN NOT BE CHANGEDYDS$0.65$0.90
84THIS PART CAN NOT BE CHANGEDYDS$0.11$0.15
85THIS PART CAN NOT BE CHANGED
86THIS PART CAN NOT BE CHANGED
87THIS PART CAN NOT BE CHANGED
88THIS PART CAN NOT BE CHANGED
89THIS PART CAN NOT BE CHANGED
90THIS PART CAN NOT BE CHANGED
91THIS PART CAN NOT BE CHANGED
92THIS PART CAN NOT BE CHANGED
93THIS PART CAN NOT BE CHANGED
94THIS PART CAN NOT BE CHANGED
95
96Total Factory Trim Cost:$1.60
97
98
99SUMMARYSUMMARY
100THIS IS TOTAL FROM ABOVE BLUE COLOR SECTIONFactory Trim Cost$1.60Factory Trim Cost
101THIS IS TOTAL FROM ABOVE GREEN COLOR SECTIONFabric Cost$2.99Fabric Cost
102THIS IS TOTAL FROM ABOVE GREEN COLOR SECTIONAccessories Cost$1.28Accessories Cost
103THIS IS FIXEDCM Cost / Pcs$2.96CM Cost / Pcs
104THIS IS FIXEDTesting Cost / Pcs$0.00Testing Cost / Pcs
105TO BE CALCULATED ON ($1.6 + $2.99 + $) X % Commercial %3.00%$0.08Commercial %3.00%
106THIS IS FIXED BASED ON %Commission %0.00%$0.00Commission %0.00%
107THIS IS TOTAL FROM ABOVETotal$8.91Total$0.00
108$8.91 IS FIRST RESULT AND NEED TO GET TO $12.00FOB$8.91FOB$12.00
Eastern
Cell Formulas
RangeFormula
J107,N107J107=SUM(J100:J106)
Cells with Data Validation
CellAllowCriteria
H20:H40List=$BQ$1:$BQ$5
H47:H68List=$BQ$1:$BQ$5
Click to expand...


Dear @footoo and @CSmith

There are several blank rows as well in each section which sometimes will be filled and sometimes will remain blank so need that point to be factored in as well.

Thanks in advance.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
884
NdNoviceHlp
NdNoviceHlp
A
  • Question
Excluding cells from calculations based on threshold
Replies
3
Views
937
andrewmurray86
A
A
  • Locked
  • Question
Break one column into multiple, based on information from another column.
Replies
1
Views
711
xenou
xenou
A
  • Question
Break one column into multiple, based on information from another column.
Replies
0
Views
709
Alex_V
A
C
  • Question
Investment rebalancing every year for a basic stock/bond allocation
Replies
0
Views
694
crmau
C

Forum statistics

Threads
1,215,147
Messages
6,123,295
Members
449,095
Latest member
Chestertim

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top