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:

Total budget

<tbody>
</tbody>
campaign1

<tbody>
</tbody>
campaign2

<tbody>
</tbody>
total spend

<tbody>
</tbody>
Fee on campain1

<tbody>
</tbody>
total fee

<tbody>
</tbody>
Fixed cost 1

<tbody>
</tbody>
Fixed cost 2

<tbody>
</tbody>
100 USD
unknown

<tbody>
</tbody>
unknown

<tbody>
</tbody>
unknown (campaign1+campain2)

<tbody>
</tbody>
unknown

<tbody>
</tbody>
(2%*campaign1)
unknown

<tbody>
</tbody>
(2,5%*total spend)
10USD10USD

<tbody>
</tbody>

<tbody>
</tbody>

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:


Total budget

<tbody>
</tbody>
campaign1

<tbody>
</tbody>
campaign2

<tbody>
</tbody>
total spend

<tbody>
</tbody>
Fee on campain1

<tbody>
</tbody>
total fee

<tbody>
</tbody>
Fixed cost 1

<tbody>
</tbody>
Fixed cost 2

<tbody>
</tbody>
100 USD=50%*total spend=50%*total spend
Put random number here

<tbody>
</tbody>
=campaign 1*2%
=total spend*2%

<tbody>
</tbody>
10USD10USD


<tbody>
</tbody>

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,
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,994
It's just a little algebra:

ABCDEFGHI
1Total BudgetCampaign1Campaign2Total SpentCampaign1 feeTotal FeeFixed cost 1Fixed cost 2
210038.64734338.64734377.2946860.772946861.932367151010
32%2.50%
4Calculated:77.294686
5Cost100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
D4=(A2-H2-I2)/(1+F3/2+G3)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



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.
 

Forum statistics

Threads
1,082,309
Messages
5,364,425
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top