Analyse all possible combinations of cheques received and identify the combination which gives maximum benefit to the Customer


Scenario 1

In a Stock Broking business (Firm), Customers deposit margin money through cheques. Although cheques are received from customers at branches, clearing (realization) of the same normally takes 3-4 days.  On one hand, the Firm wishes to allow trading to the customers against the deposited cheques from the day it receives the cheque, on other hand there is a risk associated with this  i.e cheque may get dishonored and the Firm may incur huge financial losses because of allowing customers to trade in anticipation of the cheque realization. Hence, there is a trade off between customer centricity & Financial Risk management.

The Firm may decide to take a calculated risk in order to retain its customer centric approach (it is a delightful experience for customers if they get trading benefit against the deposited cheque instantly before realization). The Firm may decide to give the benefit of cheque which are below a set threshold amount (say 50,000) instantly and for cheques which are above threshold amount the credit will be given only after realization. Sometimes there are  multiple unrealized cheques deposited by one client but the Firm needs to cap the total benefit of unrealized cheques to 50,000 (partial benefit against any cheque is not possible i.e. either a cheque will be considered in full or not considered) E.g. if a customer deposits 3 cheques of 40,000 each then also the total instant benefit against all unrealized cheques shall be capped at 50,000 - hence only one cheques of 40,000 will be credited to customers ledger before realization.

Now the question here is with respect to selection of cheques to give best results i.e. maximum total below 50,000.  Consider the following example:

Cheque1: 55,000
Cheque2: 44,000
Cheque3: 28,000
Cheque4: 7,000
Cheque5: 6,500
Cheque6: 4,000
Cheque7: 3,000
Cheque8: 500

The Firm wants to identify the "Combination of cheques" which gives the maximum benefit to the Customer subject to a limit of 50,000.  The result should be cheques 3-8.  They add up to 49,000.

Here's another example:

Cheque1: 55,000
Cheque2: 45,000
Cheque3: 25,000
Cheque4: 5,000

The result here should be cheque 2 and 4.  They add up to 50,000.

Here are my thoughts:

1. I have assumed that the total number of cheques received from customer will be capped to 8.  This limit can be relaxed but processing will then take time.

2. Create all possible combinations of cheques which can be credited.  So, since no partial credit is allowed, the cheque can either be accepted in full or be rejected.  Hence the possible values can be 2 i.e. 1 (Accepted) or 0 (Rejected).

3. So if there are 8 cheques received from a certain customer, then there will be 2^8 i.e. 256 possible combinations.  This can also be computed by using the =PERMUTATIONA(2,8) formula.  I'd like to than Saurabh Gupta for sharing the formula to generate all these combinations.

4. After creating all possible combinations, add the cheque amount for each combination.

5. Scan this total column and highlight the one which is the largest value <= the threshold value i.e. 50,000.

You may refer to my solution in the Solution worksheet of this workbook.

Scenario 2

Here's another example.  Say, one has a list of numbers as shown below:


Now one may want to know which combination of numbers adds up to or comes closest to 5115.52 (our lookup value).  If none of the combinations adds up exactly to the lookup value, then return the combination which adds to the largest value less then equal to the lookup value.  In the example above, the second, third and fourth add up to 5114.95.

You may find my solution in this workbook.

RSS 11 Comments…

 Share your views
  1. Hi Ashish,

    The topics here are different and not likely to be found on other Excel forums. I will find time and go through your site... Like you, I'm an Excel lover too, and always ready to learn the newest tips and tricks in Excel...

  2. Thanks for your quick response for my earlier query regarding the combination of numbers yeilding the zero value.

    The above solution reffered by you is not fitting my requirement.

    My requirement:

    My data consits of set of numbers which includes decimals,negative and integers, out of which excel has to show subset of numbers whose sum is zero or some specified value.

    example: consider a set of numbers -10,-1.5,1,0.5,10,200,-100,500,-100

    from the above set of data excel has to now show the subset as (-10,-1.5,1,0.5,10,200,-100,-100) since the sum of all the numbers in this subset is zero

    on real time basis, my data will be about 75 -100 numbers. so I'm looking out for a method which will throw the subset or combination of numbers whose sum is zero.

    Hope this will help u to understand more clearly.

    • Hi,

      My solution fits your requirement perfectly. Furthermore, you have shown only one combination which adds up to 0. There are as many as 6 more which do so such as -1.5,1 and 0.5; 10, -10.

      Please see my solution in this workbook.

  3. Hi Ashish,

    Thanks a ton for the solution and the sample sheet !

    On real time basis my data will have close to 100 numbers which will have 1.2676506 E 30 permutations. Which is far beyond the number of rows the excel has.

    So I think it would be fine, if i just able to find the subset covering maximum numbers rather than all the subsets whose sum is zero.

    But, i'm not sure how to approach it.

    Can you please advise me how to do it.

    Thanks again

    • Hi,

      I cannot think of a solution to that one. The fact is that to pick up the subsets(s) which cover the maximum inputs, you will first have to study all subsets. If they cannot be accommodated in the Excel row limit, then I am out of ideas.

  4. Thanks,

    I will try to think of a logic which will only show a subset covering maximum numbers whose sum is zero or some specific value.

    Once suceesfully done, will share it for the benefit of others.

  5. dear Ashish,

    you are a genius in excel.

  6. Hi Ashish,

    I am wondering if you can help me with the following problem.

    If I have multiple variables/data/values (say 3 each) in Column A, Column B and Column C. Can you find me the combination of numbers which would produce A+B-C =0. How to execute this in excel.

Leave a Comment

Your email address will not be published.