Help, formulas/goal seeking

[NNO123]

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)	10USD	10USD

<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>	10USD	10USD

<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]

It's just a little algebra:

	A	B	C	D	E	F	G	H	I
1	Total Budget	Campaign1	Campaign2	Total Spent		Campaign1 fee	Total Fee	Fixed cost 1	Fixed cost 2
2	100	38.647343	38.647343	77.294686		0.77294686	1.93236715	10	10
3						2%	2.50%
4			Calculated:	77.294686
5	Cost	100

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

Sheet2​

Worksheet Formulas Cell Formula 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.