Help, formulas/goal seeking

NNO123

New Member
Joined
Dec 29, 2017
Messages
4
Hi guys, I have this problem and cannot solve. So I was hoping on some help.
I can solve this problem with goal seeking, but this is not dynamic. Maybe you have a better solution.

So I have the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Total budget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]campaign1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]campaign2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]total spend[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="width: 130"]Fee on campain1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="width: 95"]total fee[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]Fixed cost 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94"]Fixed cost 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]100 USD[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]unknown[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]unknown[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]unknown (campaign1+campain2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]unknown[/TD]
[/TR]
</tbody>[/TABLE]
(2%*campaign1)[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]unknown[/TD]
[/TR]
</tbody>[/TABLE]
(2,5%*total spend)[/TD]
[TD]10USD[/TD]
[TD]10USD[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 873"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So I know, that the allocation between the two campaigns is 50%/50%. I know that I have 2% fee on campaign 1 and a total fee of 2,5%.

So The problem is to find the amount I can spend on campaign 1 and 2, and then I can calculate the rest. So do you think this can be done by formulas? I can solve this by using goal seeker, but this is not an dynamic solution for me, because I keep changing the fee percentages and the total budget.

In goal seek I will do:


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 86"]
<tbody>[TR]
[TD="width: 86"]Total budget[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]campaign1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="width: 84"]campaign2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 220"]
<tbody>[TR]
[TD="width: 220"]total spend[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="width: 130"]Fee on campain1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="width: 95"]total fee[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR]
[TD="width: 80"]Fixed cost 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94"]Fixed cost 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]100 USD[/TD]
[TD]=50%*total spend[/TD]
[TD]=50%*total spend[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]Put random number here[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=campaign 1*2%[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl65, width: 84"]=total spend*2%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10USD[/TD]
[TD]10USD

[/TD]
[/TR]
</tbody>[/TABLE]

Make a cell (called COST) = campaign 1+campaign2+fee on campain 1+total fee+fixed 1 + fixed 2

In goal seeker, I set my "COST" to 100 by changing "total spend".

now I will have the numbers:
campaign 1 = 38,64
Campaign 2 = 38,64
Total spend = 77,29
fee on campaign 1 = 0,77
total fee = 1,93

So everything macth. But How can I do this by formulas?? I dont think it is a difficult equation, but cannot solve it :)
any ideas? I dont mind using goal seeker, but I need it to be bore dynamic (automatically calculating if I change the percentages or my budget).

Thanks,
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
It's just a little algebra:

ABCDEFGHI
Total BudgetCampaign1Campaign2Total SpentCampaign1 feeTotal FeeFixed cost 1Fixed cost 2
Calculated:
Cost

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]38.647343[/TD]
[TD="align: right"]38.647343[/TD]
[TD="align: right"]77.294686[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.77294686[/TD]
[TD="align: right"]1.93236715[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2%[/TD]
[TD="align: right"]2.50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]77.294686[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D4[/TH]
[TD="align: left"]=(A2-H2-I2)/(1+F3/2+G3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Take the formula you have in your COST cell. Everything in it is either a constant or a function of Total Spent (D2). Express everything you can as a function of D2, then solve for D2. You'll get the formula in D4.

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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