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