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
footoo said:
"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 :
VBA Code: 
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
Click to expand...

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.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Other than a formula, could do with Goal Seek, but presumably you do not want to do manually.
 
Last edited:
Upvote 0
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.
 
Upvote 0
footoo said:
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
Click to expand...

If we need to convert the formulas to values then the whole purpose of automating the process will die :(
 
Upvote 0
shah0101 said:
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.
Click to expand...
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%?
shah0101 said:
3% commercial cost and commission
Click to expand...

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!
 
Upvote 0
CSmith said:
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!
Click to expand...

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.
shah0101 said:
3% commercial cost and commission
Click to expand...

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
ABCDEFGHIJKLMNOP
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18WITH TRUNCWITH ROUNDWITH FORMAT
19Unit Price USDTotal Amount ( in USD )
20THIS PART CAN BE CHANGEDMTR$1.02$0.95$1.57$1.58$1.58
21THIS PART CAN BE CHANGED   
22THIS PART CAN BE CHANGED   
23THIS PART CAN BE CHANGEDMTR$0.31$0.05$0.08$0.08$0.08
24THIS PART CAN BE CHANGED   
25THIS PART CAN BE CHANGED   
26THIS PART CAN BE CHANGEDMTR$0.31$0.05$0.08$0.08$0.08
27THIS PART CAN BE CHANGED   
28THIS PART CAN BE CHANGED   
29THIS PART CAN BE CHANGEDMTR$2.49$1.94$3.22$3.22$3.22
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.99PART "A"$4.97$4.97$4.97
43
44
45
46UnitPrice USDTotal Amount ( in USD )
47THIS PART CAN BE CHANGEDPCS$0.41$0.41$0.68$0.68$0.68
48THIS PART CAN BE CHANGED   
49THIS PART CAN BE CHANGED   
50THIS PART CAN BE CHANGEDPCS$0.19$0.38$0.63$0.63$0.63
51THIS PART CAN BE CHANGED   
52THIS PART CAN BE CHANGED   
53THIS PART CAN BE CHANGEDPCS$0.23$0.23$0.38$0.38$0.38
54THIS PART CAN BE CHANGED   
55THIS PART CAN BE CHANGED   
56THIS PART CAN BE CHANGEDPCS$0.05$0.05$0.08$0.08$0.08
57THIS PART CAN BE CHANGEDPCS$0.02$0.02$0.03$0.03$0.03
58THIS PART CAN BE CHANGEDPCS$0.08$0.08$0.13$0.13$0.13
59THIS PART CAN BE CHANGEDPCS$0.09$0.09$0.14$0.15$0.15
60THIS PART CAN BE CHANGEDPCS$0.02$0.02$0.03$0.03$0.03
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.28PART "B"$2.12$2.13$2.13
72
73
74
75UNITUnit Price ( in USD )Total Amount ( in USD )
76THIS PART CAN NOT BE CHANGEDCON$0.65$0.10$0.10$0.10$0.10
77THIS PART CAN NOT BE CHANGEDPCS$0.13$0.13$0.13$0.13$0.13
78THIS PART CAN NOT BE CHANGEDPCS$0.10$0.10$0.10$0.10$0.10
79THIS PART CAN NOT BE CHANGEDPCS$0.02$0.02$0.02$0.02$0.02
80THIS PART CAN NOT BE CHANGEDPCS$0.04$0.04$0.04$0.04$0.04
81THIS PART CAN NOT BE CHANGEDPCS$0.02$0.02$0.02$0.02$0.02
82THIS PART CAN NOT BE CHANGEDPCS$0.07$0.14$0.14$0.14$0.14
83THIS PART CAN NOT BE CHANGEDYDS$0.65$0.90 $0.90$0.90$0.90
84THIS PART CAN NOT BE CHANGEDYDS$0.11$0.15$0.15$0.15$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.60PART "C"$1.60$1.60$1.60
97
98
99SUMMARYSUMMARY
100THIS IS TOTAL FROM ABOVE BLUE COLOR SECTIONFactory Trim Cost$1.60PART "C"Factory Trim Cost$1.60$1.60$1.60
101THIS IS TOTAL FROM ABOVE GREEN COLOR SECTIONFabric Cost$2.99PART "A"Fabric Cost$4.97$4.97$4.97
102THIS IS TOTAL FROM ABOVE GREEN COLOR SECTIONAccessories Cost$1.28PART "B"Accessories Cost$2.12$2.12$2.12
103THIS IS FIXEDCM Cost / Pcs$2.96CM Cost / Pcs$2.96$2.96$2.96
104THIS IS FIXEDTesting Cost / Pcs$0.00Testing Cost / Pcs$0.00$0.00$0.00
105TO BE CALCULATED ON ($1.6 + $2.99 + $1.28) X % Commercial %3.00%$0.18Commercial %3.00%$0.26$0.26$0.26
106THIS IS FIXED BASED ON %Commission %1.50%$0.14Commission %1.50%$0.18$0.18$0.18
107THIS IS TOTAL FROM ABOVETotal$9.14Total$12.09$12.09$12.09
108$9.14 IS FIRST RESULT AND NEED TO GET TO $12.00$9.14FOB$12.00$12.00$12.00
109
110NEED TO GET TO $12.00:$9.14
Eastern
Cell Formulas
RangeFormula
N20:N40,N71,N47:N69,N42N20=IFERROR(IF(J20="","",TRUNC(J20*N$101/J$101,2)), "")
O20:O40,O71,O47:O69,O42O20=IFERROR(IF(J20="","",ROUND(J20*N$101/J$101,2)), "")
P20:P40,P71,P47:P69,P42P20=IFERROR(IF(J20="","",J20*N$101/J$101), "")
N76:N94,N96N76=+J76
O76:O94,O96O76=+J76
P76:P94,P96P76=+J76
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=TRUNC((O$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
P101:P102P101=TRUNC((P$108-J$108)/(J$101+(J$102-J$102*$M$105)*$M$105+J$102)*J101+J101,2)
N105N105=TRUNC(SUM(N100,N101,N102)*M105,2)
O105O105=TRUNC(SUM(O100,O101,O102)*M105,2)
P105P105=TRUNC(SUM(P100,P101,P102)*M105,2)
N106,J106N106=(N108-(N108*M106))*M106
O106O106=(O108-(O108*M106))*M106
P106P106=(P108-(P108*M106))*M106
N107:P107,J107N107=SUM(N100:N106)
J105J105=SUM(J100,J101,J102)*I105
Cells with Data Validation
CellAllowCriteria
H20:H40List=$BQ$1:$BQ$5
H47:H68List=$BQ$1:$BQ$5
 
Upvote 0
CSmith said:
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!
Click to expand...
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.
 
Upvote 0
footoo said:
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.
Click to expand...
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 %.
 
Upvote 0
shah101
I thought you were removing TRUNC from your formulas - it doesn't do what you need.
 
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,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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