Calculate Number Needed For Certain Percentage - I Have The Formulas, But How Does It Work???

[wakerider017]

I wrote this formula a long time ago and it seems to work, but I am having trouble understanding what I did.. Can someone help me figure out what is going on here?


VIPs (C2:C6)

=IF(IFERROR((-2.5*$B2)+(1.5*$A2),"")<=0,"",IFERROR((-2.5*$B2)+(1.5*$A2),""))

How many VIPs do I need to have the VIP population be 60% of the total population?

Example: If I Currently have 104 Total Customers & 59 VIPs then 56.73% of the population is VIP. If I add another 8.5 VIP customers then I end up with a perfect balance of 60% VIP and 40% regular.



Non-VIPs(D2:D6)

=IF(IFERROR(((-1*(5/3))*($A2-$B2))+((2/3)*$A2),"")<=0,"",IFERROR(((-1*(5/3))*($A2-$B2))+((2/3)*$A2),""))

How many regular customers do I need to have the VIP population be 60% of the total population?
Example: If I Currently have 89 Total Customers & 66 VIPs then 74.16% of the population is VIP. If I add another 21 regular customers then I end up with a perfect balance of 60% VIP and 40% regular.


[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]All Customers[/TD]
[TD]VIP Customers[/TD]
[TD][TABLE="width: 212"]
<tbody>[TR]
[TD="width: 212"]# of VIPs Required to be 60% VIP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 242"]
<tbody>[TR]
[TD="width: 242"]# of Regular Customers to be 60% VIP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 94"]
<tbody>[TR]
[TD="width: 94, align: right"]52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]32[/TD]
[TD][/TD]
[TD]1.33333333[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]255[/TD]
[TD]169[/TD]
[TD][/TD]
[TD]26.6666667[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]181[/TD]
[TD]108[/TD]
[TD]1.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]89[/TD]
[TD]66[/TD]
[TD][/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]104[/TD]
[TD]59[/TD]
[TD]8.5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pvr928]

Hi wakerider017

It is a little difficult to understand what you are solving for, which includes what numbers are static and what are variable.

Are 'All Customers' static, or are 'VIP Customers' static, or what is?

Cheers

pvr928

 

[wakerider017]

First 2 Columns (A and B) are static. Above formulas are in columns C and D.

 

[pvr928]

Ok - so how do you know when you need to adjust the VIP numbers or the Regular Customer numbers to get to the 60/40 ratio?

 

[wakerider017]

Ok - so how do you know when you need to adjust the VIP numbers or the Regular Customer numbers to get to the 60/40 ratio?

Not sure I follow what you are asking.

Columns C & D have the formulas I am questioning. Everything else is static. Assume this is a one time exercise, as how column A & B are updated is really irrelevant to the question. Columns C & D are showing the number of additional customers needed of each type to bring the VIP population to 60%. That could mean we need more VIPs or more regular customers. Just depends on the current mix shown in columns A & B.


The formulas work, I just want an explanation as to why they work as I seem to have forgotten what I have done.

Thank you!!!

 

[wakerider017]

No guesses?

 

[wakerider017]

This is really more of a math question than an Excel question I guess.

If there are 120 total customers and 64 are VIP and 56 are Regular. How many more VIPs would you need to have them be 60% of the population?

(-2.5*64)+(1.5*120) = 20 More VIPs are needed. This brings the total count up to 140 total customers with 84 being VIP and 56 being Regular.

Formula works. Why?

 

[wakerider017]

Gave up on that formula...

This accomplishes the same thing...

((100%/40%)*Regular Customers)-Total Customers = Number of VIPs needed make 60%

((100%/60%)*VIP Customers)-Total Customers = Number of Regular Customers needed make 60%

 

[shg]

You have this sorted now?

 

[wakerider017]

You have this sorted now?

Yep I'm all set. Thanks!