Mult. variables with known frequency. Formula?

[sosborn2]

Hi all--thanks in advance!

I have a problem which should be simple enough, but I just can't seem to find a post anywhere online that answers it.

I need to calculate commissions on accounts sold. Each account sold ($600) gets my salespeople $30. Each new account has the option of purchasing a membership. After the fifteenth membership sold, the rate goes up to $50 per account sold. In addition to accounts sold with a membership, every three accounts sold without a membership count as one membership.

For clarity:
So, if a salesperson sells five accounts, they make $150.
If they sell fifteen, and all are memberships, they make $450, but on their sixteenth, they make $50, bringing their total to $500 instead of just another $30 to $480.
If they sell twenty, but only ten are memberships, then they make $600 ($20*$30).
If they sell 30, and only ten are memberships, they then have 20/3 'equivalent memberships' which brings them up to sixteen. After the fifteenth, they make the raised rate on commissions.

I hope that makes sense.

I want to be able to solve for the number of accounts sold (and which type) in order to reach a specified payout. Since this is predictive, I can set a specific expected frequency of memberships sold and a goal payout. I recognize that the is nonlinear, so all of the $30 sales will come before the $50 ones. That's what's making this rough. I can solve this on paper, but not in Excel. I also recognize that there isn't a way to know when the memberships will show up and when they won't. The closest I can get to predicting that is by adjusting the frequency at which accounts are expected to turn to memberships.

[FDibbins]

Hi and welcome to the forum

It might help if you provided a sample workbook for use to play with, showing a few sample answers and how you arrived at them?

On the face of it, it sounds like a fairly simple (nested) if() statement with a step for +16, but Im not quite sure how you figure the "membership" into this

[sosborn2]

Thanks.

I don't know how clear I made this last time, but I simply want to be able to enter in a value for "Pay" and have Excel provide the number of total accounts and the number of accounts with memberships given the % of accounts with memberships and the commission structure.