Calculating a Number from the very number it is based on

S

shah0101

Board Regular
Joined
Jul 4, 2019
Messages
139
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:
Sort by date Sort by votes

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
shah0101 said:
N101 and N102 equal % adjustment is needed please.
No, $12 varies from one cost sheet to another.
Click to expand...
Good now we're getting somewhere . . .
How will the $12 vary? % of margin? or something else?
 
Upvote 0
CSmith said:
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 . . .
Click to expand...
@CSmith as I mentioned earlier, $12 is not fixed. it could be $13 in the next sheet and $15 in the next sheet. This $12 price comes as word of mouth from bosses and we have to adjust that difference in the cost sheet.
 
Upvote 0
footoo said:
The figure of 12 is not important and does not affect the method of calculation.
Click to expand...
Actually it is it determines how much % I apply to each value to find some predetermined end result like ceiling of the next 4$ or something . . . Something or some reason for choosing the $12 as the appropriate value...
 
Upvote 0
footoo said:
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.
Click to expand...
@footoo I already did that in P column so.... I am just keeping other two to see if there is any decimal difference among the three.
 
Upvote 0
CSmith said:
Actually it is it determines how much % I apply to each value to find some predetermined end result like ceiling of the next 4$ or something . . . Something or some reason for choosing the $12 as the appropriate value...
Click to expand...

That is exactly the problem that is why the heading is "Calculating a Number from the very number it is based on" and i tried many way sometime ended up with errors and most of the times ended up with cross referencing..... This is the reason I came to experts!
 
Upvote 0
shah0101 said:
just word of mouth, bosses gives the sheet and tell us make $12 for this one, make $13 for this one and so on....
Click to expand...
Ok, so using what you just said you will have a number and then enter $12 into a desired value area and voila I can now give you $12 as a result from the above. . .

Book1
HIJKLMNOPQR
99SUMMARYSUMMARY
100Factory Trim Cost1.6PART "C"Factory Trim Cost1.61.61.61.6000$1.60
101Fabric Cost2.99PART "A"Fabric Cost4.974.984.9753512031.4950$4.84
102Accessories Cost1.28PART "B"Accessories Cost2.122.132.1299162340.6400$2.07
103CM Cost / Pcs2.96CM Cost / Pcs2.962.962.962.9600$2.96
104Testing Cost / Pcs0Testing Cost / Pcs0000$0.00
105Commercial %3.00%0.1761Commercial %3.00%0.260.260.2611580230.1121$0.35
106Commission %1.50%0.1350435Commission %1.50%0.17730.17730.17730.1773$0.18
107Total9.1411435Total12.087312.107312.103725466.9844$12.00
1089.14FOB12121212$12.00
109Desired FOB $12
Sheet2
Cell Formulas
RangeFormula
N100:P100N100=+N96
N101:N102N101=TRUNC((N$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
O101:O102O101=ROUND((O$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
P101:P102P101=((P$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101)
Q100,Q103Q100=J100
R100,R103R100=J100
Q101:Q102Q101=J101/2
R101:R102R101=ROUND($J101*(1+(($J$101+$J$102)/(DesiredFOB-(SUM(R$103:R$106,R$100))))),2)
N105N105=TRUNC(SUM(N100,N101,N102)*M105,2)
O105O105=ROUND(SUM(O100,O101,O102)*M105,2)
P105P105=(SUM(P100,P101,P102)*M105)
Q105Q105=M105*SUM(Q100:Q102)
N106,Q106N106=(N108-(N108*$M106))*$M106
O106O106=(O108-(O108*M106))*M106
P106P106=(P108-(P108*M106))*M106
N107:R107,J107N107=SUM(N100:N106)
J105J105=SUM(J100,J101,J102)*I105
J106J106=(J108-(J108*I106))*I106
R105:R106R105=DesiredFOB-(DesiredFOB/(1+$I105))
R108R108=SUM(R100:R106)
Named Ranges
NameRefers ToCells
DesiredFOB=Sheet2!$J$109R101:R102, R105:R106
 
Upvote 0
Opps didnt mean to leave the column before last one there:
Book1
HIJKLMNOPQ
99SUMMARYSUMMARY
100Factory Trim Cost1.6PART "C"Factory Trim Cost1.61.61.6$1.60
101Fabric Cost2.99PART "A"Fabric Cost4.974.984.975351203$4.84
102Accessories Cost1.28PART "B"Accessories Cost2.122.132.129916234$2.07
103CM Cost / Pcs2.96CM Cost / Pcs2.962.962.96$2.96
104Testing Cost / Pcs0Testing Cost / Pcs000$0.00
105Commercial %3.00%0.1761Commercial %3.00%0.260.260.261158023$0.35
106Commission %1.50%0.1350435Commission %1.50%0.17730.17730.1773$0.18
107Total9.1411435Total12.087312.107312.10372546$12.00
1089.14FOB121212$12.00
109Desired FOB $12
110NEED TO GET TO $12.00:9.14
Sheet2
Cell Formulas
RangeFormula
N100:P100N100=+N96
N101:N102N101=TRUNC((N$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
O101:O102O101=ROUND((O$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
P101:P102P101=((P$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101)
Q100,Q103Q100=J100
Q101:Q102Q101=ROUND($J101*(1+(($J$101+$J$102)/(DesiredFOB-(SUM(Q$103:Q$106,Q$100))))),2)
N105N105=TRUNC(SUM(N100,N101,N102)*M105,2)
O105O105=ROUND(SUM(O100,O101,O102)*M105,2)
P105P105=(SUM(P100,P101,P102)*M105)
N106N106=(N108-(N108*$M106))*$M106
O106O106=(O108-(O108*M106))*M106
P106P106=(P108-(P108*M106))*M106
N107:Q107,J107N107=SUM(N100:N106)
J105J105=SUM(J100,J101,J102)*I105
J106J106=(J108-(J108*I106))*I106
Q105:Q106Q105=DesiredFOB-(DesiredFOB/(1+$I105))
Q108Q108=SUM(Q100:Q106)
Named Ranges
NameRefers ToCells
DesiredFOB=Sheet2!$J$109Q101:Q102, Q105:Q106
 
Upvote 0
Apologies I messed up formulas when copying them over...

Book1
HIJKLMNOPQ
99SUMMARYSUMMARY
100Factory Trim Cost1.6PART "C"Factory Trim Cost1.61.61.6$1.60
101Fabric Cost2.99PART "A"Fabric Cost4.974.984.975351203$5.51
102Accessories Cost1.28PART "B"Accessories Cost2.122.132.129916234$2.36
103CM Cost / Pcs2.96CM Cost / Pcs2.962.962.96$2.96
104Testing Cost / Pcs0Testing Cost / Pcs000$0.00
105Commercial %3.00%0.1761Commercial %3.00%0.260.260.261158023$0.38
106Commission %1.50%0.1350435Commission %1.50%0.17730.17730.1773$0.19
107Total9.1411435Total12.087312.107312.10372546$13.00
1089.14FOB121212$13.00
109Desired FOB $13
110NEED TO GET TO $12.00:9.14
Sheet2
Cell Formulas
RangeFormula
N100:P100N100=+N96
N101:N102N101=TRUNC((N$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
O101:O102O101=ROUND((O$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
P101:P102P101=((P$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101)
Q100,Q103Q100=J100
Q101Q101=ROUND(J101*(DesiredFOB-SUM(Q103:Q106,Q100))/SUM($J$101:$J$102),2)
Q102Q102=ROUND($J$102*(DesiredFOB-SUM(Q103:Q106,Q100))/SUM($J$101:$J$102),2)
N105N105=TRUNC(SUM(N100,N101,N102)*M105,2)
O105O105=ROUND(SUM(O100,O101,O102)*M105,2)
P105P105=(SUM(P100,P101,P102)*M105)
N106N106=(N108-(N108*$M106))*$M106
O106O106=(O108-(O108*M106))*M106
P106P106=(P108-(P108*M106))*M106
N107:Q107,J107N107=SUM(N100:N106)
J105J105=SUM(J100,J101,J102)*I105
J106J106=(J108-(J108*I106))*I106
Q105:Q106Q105=DesiredFOB-(DesiredFOB/(1+$I105))
Q108Q108=SUM(Q100:Q106)
Named Ranges
NameRefers ToCells
DesiredFOB=Sheet2!$J$109Q101:Q102, Q105:Q106
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P
C
  • Solved
Create Function that Calculates a Value Relative to Original Price and Also Constructs a Price Ladder
2
Replies
10
Views
1K
NdNoviceHlp
NdNoviceHlp
A
  • Question Question
Excluding cells from calculations based on threshold
Replies
3
Views
1K
andrewmurray86
A
A
  • Locked
  • Question Question
Break one column into multiple, based on information from another column.
Replies
1
Views
831
xenou
xenou
A
  • Question Question
Break one column into multiple, based on information from another column.
Replies
0
Views
809
Alex_V
A

Forum statistics

Threads
1,223,897
Messages
6,175,270
Members
452,628
Latest member
dd2

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