Spreadsheet: SUMIF & ARRAY

Oracle / Openoffice.org
December 25, 2010 at 12:58:54
Specs: Windows Vista
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 attempt as a starting point.

See More: Spreadsheet: SUMIF & ARRAY

Report •

December 25, 2010 at 16:10:24
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.

Report •
Related Solutions

Ask Question