Help, formulas/goal seeking

N

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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

S
  • Question Question
Optimizing Price with Weightings to get to Best Price Mix - Use Solver?
Replies
1
Views
318
StephenCrump
S
M
  • Solved
This is really a maths question... calc a % fee to charge based on a number that is only found when you take away the fee... :/
Replies
7
Views
385
RobP
R
V
  • Solved
Goal Seek or What if Analysis to be used in order not to calculate the total revenue beyond certain date
Replies
4
Views
342
SanjayGMusafir
SanjayGMusafir
M
  • Question Question
Total dollar amount if hours are over 40.
2
Replies
11
Views
693
awoohaw
awoohaw
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
2K
felixstraube
F

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

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