Formula Help - 2 dynamic variables

[eoinymc]

Hi,

Just wondering if anyone can help me.

I'm currently trying to solve an equation and put it into an excel formula.

If I have the following info:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customers[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Total Revenue[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]Revenue Per Customer[/TD]
[TD]$20[/TD]
[/TR]
</tbody>[/TABLE]

I want to increase my total revenue by $100 and the increase will be gotten by 50% of revenue increase and 50% of customer increase.

Can anyone help me out on how to do this please?

If you need any more info, please let me know.

Cheers,

E

 

[eoinymc]

I meant to say that I'm looking for a way to figure out what my new average revenue per customer is and what my new total customers are if I get the required increase by increasing both by 50%...

 

[oldbrewer]

[TABLE="width: 924"]
<colgroup><col><col><col span="12"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]customers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]280[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]320[/TD]
[TD="align: right"]340[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20.5[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]225.5[/TD]
[TD="align: right"]246[/TD]
[TD="align: right"]266.5[/TD]
[TD="align: right"]287[/TD]
[TD="align: right"]307.5[/TD]
[TD="align: right"]328[/TD]
[TD="align: right"]348.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]231[/TD]
[TD="align: right"]252[/TD]
[TD="align: right"]273[/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]315[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]357[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]revenue per customer[/TD]
[TD][/TD]
[TD="align: right"]21.5[/TD]
[TD="align: right"]215[/TD]
[TD="align: right"]236.5[/TD]
[TD="align: right"]258[/TD]
[TD="align: right"]279.5[/TD]
[TD="align: right"]301[/TD]
[TD="align: right"]322.5[/TD]
[TD="align: right"]344[/TD]
[TD="align: right"]365.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]264[/TD]
[TD="align: right"]286[/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]352[/TD]
[TD="align: right"]374[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22.5[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]247.5[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]292.5[/TD]
[TD="align: right"]315[/TD]
[TD="align: right"]337.5[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]382.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]253[/TD]
[TD="align: right"]276[/TD]
[TD="align: right"]299[/TD]
[TD="align: right"]322[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]368[/TD]
[TD="align: right"]391[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23.5[/TD]
[TD="align: right"]235[/TD]
[TD="align: right"]258.5[/TD]
[TD="align: right"]282[/TD]
[TD="align: right"]305.5[/TD]
[TD="align: right"]329[/TD]
[TD="align: right"]352.5[/TD]
[TD="align: right"]376[/TD]
[TD="align: right"]399.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]264[/TD]
[TD="align: right"]288[/TD]
[TD="align: right"]312[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]384[/TD]
[TD="align: right"]408[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24.5[/TD]
[TD="align: right"]245[/TD]
[TD="align: right"]269.5[/TD]
[TD="align: right"]294[/TD]
[TD="align: right"]318.5[/TD]
[TD="align: right"]343[/TD]
[TD="align: right"]367.5[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]416.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]325[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]375[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]425[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25.5[/TD]
[TD="align: right"]255[/TD]
[TD="align: right"]280.5[/TD]
[TD="align: right"]306[/TD]
[TD="align: right"]331.5[/TD]
[TD="align: right"]357[/TD]
[TD="align: right"]382.5[/TD]
[TD="align: right"]408[/TD]
[TD="align: right"]433.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]260[/TD]
[TD="align: right"]286[/TD]
[TD="align: right"]312[/TD]
[TD="align: right"]338[/TD]
[TD="align: right"]364[/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]416[/TD]
[TD="align: right"]442[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]265[/TD]
[TD="align: right"]291.5[/TD]
[TD="align: right"]318[/TD]
[TD="align: right"]344.5[/TD]
[TD="align: right"]371[/TD]
[TD="align: right"]397.5[/TD]
[TD="align: right"]424[/TD]
[TD="align: right"]450.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]297[/TD]
[TD="align: right"]324[/TD]
[TD="align: right"]351[/TD]
[TD="align: right"]378[/TD]
[TD="align: right"]405[/TD]
[TD="align: right"]432[/TD]
[TD="align: right"]459[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27.5[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]302.5[/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]357.5[/TD]
[TD="align: right"]385[/TD]
[TD="align: right"]412.5[/TD]
[TD="align: right"]440[/TD]
[TD="align: right"]467.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]280[/TD]
[TD="align: right"]308[/TD]
[TD="align: right"]336[/TD]
[TD="align: right"]364[/TD]
[TD="align: right"]392[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]448[/TD]
[TD="align: right"]476[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28.5[/TD]
[TD="align: right"]285[/TD]
[TD="align: right"]313.5[/TD]
[TD="align: right"]342[/TD]
[TD="align: right"]370.5[/TD]
[TD="align: right"]399[/TD]
[TD="align: right"]427.5[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]484.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]319[/TD]
[TD="align: right"]348[/TD]
[TD="align: right"]377[/TD]
[TD="align: right"]406[/TD]
[TD="align: right"]435[/TD]
[TD="align: right"]464[/TD]
[TD="align: right"]493[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29.5[/TD]
[TD="align: right"]295[/TD]
[TD="align: right"]324.5[/TD]
[TD="align: right"]354[/TD]
[TD="align: right"]383.5[/TD]
[TD="align: right"]413[/TD]
[TD="align: right"]442.5[/TD]
[TD="align: right"]472[/TD]
[TD="align: right"]501.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]390[/TD]
[TD="align: right"]420[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]480[/TD]
[TD="align: right"]510[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]clearly 13 customers and $23 per customer is one solution[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]as is 14 customers and $21.5 per customer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]you have to decide what is possible[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[eoinymc]

Thanks for the help..

It's not quite what I was looking for. I wanted a way to do this by being able to change the 50%/50% split in a cell and a formula automatically updating what would show what this meant for increase in customers and revenue.

But I appreciate that.

E

 

[oldbrewer]

ok I did not "see" that in your original post - I will give it more thought...

 

[eoinymc]

ok I did not "see" that in your original post - I will give it more thought...

Thanks so much...it's hurting my head!

 

[oldbrewer]

[TABLE="width: 746"]
<tbody>[TR]
[TD="align: right"]10[/TD]
[TD]customers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]custincr%[/TD]
[TD="colspan: 2"]percustincr%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$20[/TD]
[TD]per customer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$200[/TD]
[TD]REVENUE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]customers[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]per cust[/TD]
[TD="align: right"]31.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]REVENUE[/TD]
[TD="align: right"]$434.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]now you can play with the % split to study the effect on total revenue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]if this is ok with you, I will post formulas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[eoinymc]

I don't think I'm explaining it correctly.

The target revenue to hit is $300 (which is an increase of $100). This is a constant.

How I get that extra $100 is a mix of increasing customers and revenue per customer.

50% of the increase should come from increasing customers and 50% from increasing the revenue per customer. i.e. in this example, $50 incremental revenue from increasing rev per customer and $50 from increasing customers.

Then, what I want to be able to do is to change 50-50 to 60-40 or whatever and it shows me what the mix of increases will be.

Does that explain it?

Thanks again for your help thus far.

E

 

[Noodleski]

12,5 customers is a bit weird, isn't it? I guess that's what oldbrewer tried to avoid.

 

[oldbrewer]

if you get X more customers and Y more revenue you get an equation 20X +XY+10Y = 100

clearly there are infinite solutions.....my table shows you this and my post 7 gives you a way to look at the effects of varying the split percentages