Formula required to minus 2 units from 4 column values in the descending order..

Dear All,

I'm facing a tricky query and I'm not sure how do I write code for this logic. Request you to help me in this solving this query.

This query is to compute sales agents incentives selling cards. In our incentive structure, it is proposed that the sales agent will get his incentives from 3 card. The first two cards which will be knocked off is any two cheap cards in the order of Silver, Gold, Platinum & Black.

Below is a sample dataset with input values (Agent, Silver IP, Gold IP, Platinum IP, Black IP and the sum of all input columns). Next to it is the output dataset which has 2 card units less as per the plan.

Agent A1 has sold 1 Gold, 1 Platinum and 4 black cards and the output grid will have only 4 black cards for payout. Gold & Platinum being cheaper than Black will be knocked off.
For A2, Silver and Platinum (1 card each) will be knocked off and he will be paid incentives only on 2 black cards.
For A3, 2 silver cards will be knocked off and incentives will be paid on Gold, Platinum and Black card.
For A4, no cards sold hence no incenitves
For A5, only 1 black card sold and will be knocked off hence no payout
For A6, only 2 cards sold and both will be knocked off hence no payout.
For A7, 2 cards from Silver will be knocked off and will be paid on 3 silver, 3 gold, 1 Platinum & 2 black cards.
For A9, only 2 black cards sold which will get knocked off and hence no payout.

Thanks a lot in advance...


Agent - Silver IP - Gold IP - Platinum IP - Black IP - Sum IP :-: Silver OP - Gold OP - Platinum OP - Black OP - Sum OP
A1 - 0 - 1 - 1 - 4 - 6 :-: 0 - 0 - 0 - 4 - 4
A2 - 1 - 0 - 1 - 2 - 4 :-: 0 - 0 - 0 - 2 - 2
A3 - 2 - 1 - 1 - 1 - 5 :-: 0 - 1 - 1 - 1 - 3
A4 - 0 - 0 - 0 - 0 - 0 :-: 0 - 0 - 0 - 0 - 0
A5 - 0 - 0 - 0 - 1 - 1 :-: 0 - 0 - 0 - 0 - 0
A6 - 0 - 1 - 1 - 0 - 2 :-: 0 - 0 - 0 - 0 - 0
A7 - 5 - 3 - 1 - 2 - 11 :-: 3 - 3 - 1 - 2 - 9
A8 - 3 - 1 - 1 - 1 - 6 :-: 1 - 1 - 1 - 1 - 4
A9 - 0 - 0 - 0 - 2 - 2 :-: 0 - 0 - 0 - 0 - 0


Regards, Anil
Mob : +971(56) 6829672
Email : anil.jagtap@gmail.com (anil dot jagtap at gmail dot com)

In G3 you need this formula:

=MAX(0,B3-2)

And then, in H3 this one:

=MAX(0,C3-(2-(SUM($B3:B3)-SUM($G3:G3))))

Which is then copied across to I3 and J3.

The whole row from G3:J3 can then be copied down to give you the answers you're looking for.

Thanks Andrew, This formula worked...

Originally Posted by Andrew-R

In G3 you need this formula:

=MAX(0,B3-2)

And then, in H3 this one:

=MAX(0,C3-(2-(SUM($B3:B3)-SUM($G3:G3))))

Which is then copied across to I3 and J3.

The whole row from G3:J3 can then be copied down to give you the answers you're looking for.