How can I create a formula for "payout" that computes a commission for a magazine sales person such that:

1. the salesperson gets 2% for the 1st month of a person subscribes, 4% of the second month, 6% of the 3rd, and 8% of the 4th.

2. if a person renews their subscription after a lapse, the salesperson does not get any commission if the salesperson has already gotten 4 months of commission

3. if a subscriber is not "eligible" then the salesperson gets no commission at all from them

See my feeble attemptas a starting point.

The easy answer is that I don't believe that you will be able to accomplish your goal with a formula because of item 2. There is no formula that will "find" a zero in a column and then look upwards and count to see if there are 4 of something else above it.

Formulas can not perform actions like that.

It's probable that your goal could be accomplished with VBA, but I have a question:

Your example spreadsheet (BTW thanks for posting that) shows what appears to be 3 subscribers. Can I assume that in real life that are more than just 3? How many columns would we really be dealing with.

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

Ask Your Question

Weekly Poll