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

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History