Tags: SUMPRODUCT

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

{0 Comments}

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.

Prioritise investment liquidation to minimise Capital Gains

{2 Comments}

Assume a five column dataset with the Fund Name in column A, Market Value in column B, Gain/Loss in column C, Gain/Loss % in column D [column C/column B] and Type of investment in column E (Short term or Long term).  Since multiple investments can be made in one fund, there can be multiple line items for with the same Fund Name (with different market values).  Let's call this dataset the Investment schedule.

The objective is to determine the following:

a. Minimum total gain realised from sale of one/multiple funds; and
b. Short term gain present in a above; and
c. Total Long term gain realised from sale of one/multiple funds; and
d. If Long term investments fall for liquidating the particular fund, then how much is the gain/loss by forcible liquidating the short term investments.

You may refer to my solution in this workbook.

Consider a Pivot Table Value field column as a criteria for computing another Value Field column

{8 Comments}

Assume a simple three column dataset showing hours worked by different machine on different dates.  So column A is Date, column B is Machine Name and column C is hours worked.  There are duplicates appearing in column A and B .  Blanks in column C depict machine idle time.

The task is to create a simple three column dataset showing all unique Machine names in the first column, Last day on which the machine worked in the second column and hours worked on the last day in the third column.

This problem can be solved by using formulas (Refer first worksheet of the workbook) but if one has to use a Pivot Table, then there would be a few problems.

1. The Grand Total for the Date Field should be blank because on cannot determine the Last day on which the machine worked across different machine types.  A conventional Pivot Table shows the Maximum of all dates appearing in the Date Field.

2. The Grand Total for the Hours worked Field should be a summation of the total hours worked on last day across all machine types.  A conventional Pivot Table shows the Maximum of all hours worked appearing in the Hours worked Field.

3. The biggest problem of them all is that there is no way to give a criteria as the Last day for that machine for computing another Field in the Pivot Table.  Please refer the file for a better understanding.

This problem can be solved using the PowerPivot.  You may refer to my solution in this workbook.

Ignore errors while adding non contiguous cells of a range

{7 Comments}

In range E5:E13, assume the entries are

1
2
#DIV/0!
4
5
6
#N/A
8
9

In cell I9, one may want to sum up the figures from cells E5,E7,E11 and E13. If one supplies the individual cell references to the SUM function, then the result will be a #DIV/0! error because the SUM function is intolerant towards errors supplied in its range. The formula which will resolve this problem is:

=SUMPRODUCT(SUMIF(INDIRECT(A11:A14),">-1E100"))

A11:A14 has E5,E7,E11 and E13.

Compute configuration count using Set Theory and Venn Diagrams

{0 Comments}

Assume a table which lists attendees for a Company's Annual day function.  In this Table, data for every attendee is shown on a separate row so if an employee attends the function with his/her spouse and three children, then there will be 5 rows for that employee.

The question is to determine the count of the following family configuration:

1. Employees only (those who attended without spouse and children); and
2. Employees, spouse and children (Family); and
3. Employees and spouse (no children); and
4. Employees and children (no spouse)

You may refer to my solution in this workbook.  I have solved this problem using:

1. MS Excel Formulas based on Set Theory and Venn Diagram; and
2. PowerPivot

Perform an iterative sum of Top n values across multiple columns

{4 Comments}

A tournament has 18 participating teams with 25 players in each team.  Each team has to play five rounds of the Tournament and not all players play all rounds.  Scores earned by each player in each round are shown in individual cells.  If a player does not play a round, that cell is left empty.

The task is to sum the highest 18 scoring players for each round.  Only the highest 18 players per team count towards the teams score.  If few of the players have the same score at position 18 then only one of them should be included in the overall score.

One solution is to sort each round of scores for each team in descending order and sum the highest 18 values.  This is obviously a time consuming process.

There could be two others ways to solve this problem

Formula driven solution - This uses a spare column, a lengthy formula and the Data > Table functionality.  Since Data > Table is a series of array formula, this solution makes the workbook very sluggish.  You may refer to my solution in this workbook.

PowerPivot solution - This solution is far better than the formula driven one in as much as no spare columns, lengthy formulas or Data > Tables have been used.  The solution in this workbook adds the scores of the highest 18 scoring players per round (If few of the players have the same score at position 18 then all scores are included in the overall score).  To use this PowerPivot solution, you need to be using the PowerPivot add-in for MS Excel.  This add-in is only available for Excel 2010 and higher versions.

Determine cumulative expenses per employee when per diem rates vary by block of dates

{8 Comments}

Assume per diem travel rates vary by block of dates (from and to).  So, assume the per diem rate for travel dates between 26/2/2013 and 28/2/2013 is Rs. 78,000/day.  Likewise, if a person travels between 1/3/2013 and 25/3/2013, the per diem rate applicable is Rs. 70,000/day.  With different travel dates (from and to) specified per traveller, the task is to determine total travel expenses per individual.

You may refer to my solution in this workbook.

Summarise data from multiple sheets with multiple conditions – Part II

{64 Comments}

Assume date wise revenue data has been shown on two sheets - Sports wear and Fitness equipment (Data worksheets).  On each of these two worksheets, there are two columns - Date and Revenue.  On the Summary worksheet, the user would like to summarise data (Count instances and sum revenue figures) based on multiple conditions.  The conditions for summarising are:

1. User specifies upper and lower revenue figures - Revenue figures from both sheets which satisfy the conditions (two conditions) should get added as well as rows should get counted (to know instances)
2. User specifies upper and lower date limits - Revenue figures from both sheets which satisfy the conditions (two conditions) should get added as well as rows should get counted (to know instances)
3. User specifies upper and lower revenue figures AND User specifies upper and lower date limits - Revenue figures from both sheets lying between these figures and dates should get added as well as rows should get counted (to know instances)

The restrictions are as follows:

1. Do not want to consolidate data from two data worksheets into a single worksheet
2. Do not want to perform any ancillary calculations on the two data worksheets

If these conditions are relaxed, then alternative solution such as Pivot Tables and SUMPRODUCT() function can be used.

You may refer to my solution in this workbook.

I have solved a related problem of summarising data from multiple worksheets here.

Show granular as well as total figures on the Summary sheet

{0 Comments}

Assume there is data for complaints received and complaints resolved for two years - 2009 and 2010 for some regions regions.  Each Region has its own worksheet.  There are three headings on each sheet - Particulars, 2009 and 2010.  While the Particulars column has the text "Complaints received" and "Complaints resolved", the year columns have number of complaints received and resolved.

The task is to create a Summary sheet which:

1. Show the consolidated figure of complaints received and complaints resolved for both years
2. Show the individual regions which make up the consolidated figure in 1 above.

In this workbook, you may see the data layout and four different ways of resolving the two tasks mentioned above.

You may also refer to the following related posts:

1. Summarise data from multiple worksheets with one condition
2. Summarise data from multiple worksheets with one condition - Part II
3. Summarise data from multiple worksheets
4. Summarise data from multiple worksheets with multiple conditions
5. Summarise data from multiple worksheets with multiple conditions - Part II
6. Summarise data from different cells of multiple worksheets

Count unique values with conditions on large databases

{8 Comments}

Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all.

In this workbook, I have shown the technique to count unique values with conditions on a large database

1. Using PowerPivot - Will only work in Excel 2010 and higher versions

2. Using a  normal Pivot Table and SUMPRODUCT() function - Will work for all versions but is not as efficient as the PowerPivot solution.

To count unique values with conditions on small databases, you may refer to the following link