# Help, formulas/goal seeking

#### NNO123

##### New Member
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
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

</tbody>
Sheet2

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

</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.

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

### 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...