Tags: ROW

Return the specific product which satisfies the user defined feature combination

{2 Comments}

Here is a simple matrix like data layout which shows the features available in every product.  Let's assume that this data is in range A2:E8 (including the header row)

Functions Product A Product B Product C Product D
Function 1 x
Function 2 x x
Function 3 x x x
Function 4 x x
Function 5 x x
Function 6 x

Here is a user created input table of his/her requirements.  Let's assume that this data is in range A12:B18 (including the header row)

Functions Input
Function 1
Function 2 x
Function 3 x
Function 4
Function 5 x
Function 6

The expected result is the "Product" which meets the user defined function combinations. The result should be Product B. If there are different products which meet the user's requirements, only the first one will be returned as a result. If one would like all products to be returned, the one can use Power Query to resolve this problem.

=IFERROR(INDEX($B$2:$E$2,1,MATCH(COUNTA($A$13:$A$18),MMULT(1*(TRANSPOSE(B3:E8=B13:B18)),1*(ISNUMBER(ROW(INDIRECT("1:"&COUNTA($A$3:$A$8)))))),0)),"No such product")

Please note that this is an array formula so please confirm the formula with Ctrl+Shift+Enter.

Determine cumulative interest payable on an annuity with varying time periods

{0 Comments}

Imagine a fixed monthly amount due to an Organisation for services rendered to various customers.  While an invoice is raised every month by this Organisation, not all pay up the dues on time.  For unpaid dues, the Organisation charges its client interest ranging from 3% to 9% per annum.  The objective is to determine cumulative interest payable by various customers to Organisation X.

The base data looks like this

Client Monthly revenue Int. calculation start date Int. calculation end date Interest rate
Client A 33,967 01-Aug-16 25-Jul-17 9.00%
Client B 123 12-Sep-16 30-Nov-17 4.00%

Given the dataset above, the total interest payable by Client A is Rs. 16,237.20.  The calculation is shown below:

From To Days for which interest should be paid Principal Interest
02-Aug-16 31-Aug-16 328.00 33,967.00 2,745.26
01-Sep-16 30-Sep-16 298.00 33,967.00 2,494.17
01-Oct-16 31-Oct-16 267.00 33,967.00 2,234.71
01-Nov-16 30-Nov-16 237.00 33,967.00 1,983.62
01-Dec-16 31-Dec-16 206.00 33,967.00 1,724.16
01-Jan-17 31-Jan-17 175.00 33,967.00 1,464.70
01-Feb-17 28-Feb-17 147.00 33,967.00 1,230.34
01-Mar-17 31-Mar-17 116.00 33,967.00 970.88
01-Apr-17 30-Apr-17 86.00 33,967.00 719.79
01-May-17 31-May-17 55.00 33,967.00 460.33
01-Jun-17 30-Jun-17 25.00 33,967.00 209.24
01-Jul-17 25-Jul-17 - 33,967.00 -
Total       16,237.20

You may download my solution workbook with from here. I have solved this problem using normal Excel formulas and the PowerPivot.

Determine number of learners who have completed different stages of multiple online courses

{4 Comments}

Here is a sample dataset of learners who have cleared different stages of multiple courses on offer within an Organisation:

Learner Stage completed Course
Bill Stage 1 Public Speaking
Bill Stage 2 Public Speaking
Bill Stage 3 Public Speaking
Susan Stage 1 Effective Communication
Bob Stage 1 Public Speaking
Bob Stage 2 Public Speaking
Sheila Stage 1 Effective Communication
Sheila Stage 2 Effective Communication
Sheila Stage 3 Effective Communication
Frank Stage 1 Effective Communication
Frank Stage 2 Effective Communication
Henry Stage 1 Public Speaking
Henry Stage 2 Public Speaking
Bill Stage 1 Effective Communication
Bill Stage 2 Effective Communication

From this sample dataset, one may want to know how many participants have completed each stage of these multiple courses.  The expected result is shown below:

Row Labels Stage 1 Stage 2 Stage 3
Effective Communication 1 2 1
Public Speaking 2 1
Grand Total 1 3 2

In this workbook, I have shared 2 solutions - one using formulas and the other using the Power Query & PowerPivot.

Return best possible fit, to manually entered dimensions, with the intent to minimise wastage

{5 Comments}

Refer to a simple 5 column representative inventory dataset of a Glass manufacturer:

Model Length (MM) Wide (MM) Thk (MM) CAT
HX9-G-ARD 1071 273 3.5 A
MYP-G-3RD 580 535 3.2 B
EPO-G-3RD 580 535 3.2 A
MYG-G-3R 966 350 3.2 A
MYN-G-3RD 649 530 3.2 A
GM SPIN-G-3FD 882 395 3.2 A
MY8-G-AR 880 400 3.5 B
GM2-G-AR 880 400 3.5 A

From this inventory data, one has to furnish customer orders based on specific dimensions demanded by them.  A typical Customer request would be to supply glass sheets as per the following dimensions

Length (MM) Wide (MM) Thk (MM) CAT
780 542 3.5 A

The firm may or may not have glass sheets of this specific size.  The objective is to identify glass sheets, from the inventory on hand, which match customer specifications.  If there is no exact match, then one must be able to obtain all inventory items which have the same Thk (MM) and CAT as the customer specified dimensions but the Length and thickness should be more than equal to the customer specified dimensions.  The length and width can then be trimmed to match the exact customer dimensions.  Furthermore, the result returned should:

  1. List only the Top 30 glass sheets available in inventory; and
  2. List those Top 30 glass sheets in ascending order of wastage (wastage caused when the glass sheet is trimmed to match the customer specified dimensions)

You may refer to my solution in this workbook.  I have shared two solutions - one using Excel formulas and the other using Power Query a.k.a. Get and Transform in Excel 2016.  Please read the Comments in cells F1, J9 and J16 of the "Solutions" worksheet.  The difference between the 2 solutions is:

  1. Formula driven solution - This is in range J10:AM14 of the Solutions worksheet.  This is a semi dynamic solution (as compared to the Power Query solution).  To get the models in ascending order of wastage, one will have to create an Area column in the base data and sort that column in ascending order.
  2. Power Query solution - This is in range J17:AM21 of the Solutions worksheet.  This is a dynamic solution.  Just change the customer specified dimensions in range G2:J2 of the Data and Query worksheet.  Thereafter just right click on any cell in the range below and select refresh.

Convert a text entry into its number equivalent

{0 Comments}

Consider this simple two column table showing text entries in column A and the corresponding numbers in column.  Assume this data is in range A2:B11 (headings are in A1:B1).

text Value
A 1
B 2
C 3
D 4
E 5
F 6
G 7
H 8
I 9
J 0

The objective is to generate the numeric code for text code of any length entered in a certain cell.  For example, a user will type a certain text code, say ABEJ and the expected result should be 1250.  For JABF, the result should be 0126.  The text entry and text length are both user determined.

With ABEJ, typed in cell D2, enter this array formula in cell E2

=TEXT(SUMPRODUCT((LOOKUP(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1),$A$2:$A$11,$B$2:$B$11))*((10^(LEN(D2)-1-(ROW(INDIRECT("1:"&LEN(D2)))-1))))),REPT("0",LEN(D2)))

This formula can now be copied down for generating the numeric code for all text codes entered in column D.

Remove special characters from a string

{2 Comments}

Hi,

Assume a column of names as follows:

Name
Mohammed Zia-Ul Haque
Steven Thomas -
,-Rohit Sunil Ahir-Chowdhary.-
Anuj -----------
Sameer --
..,Mohit --
Rajeev Nair.
Monalisa . Das
Vijeta ...
--,.Anjana. M.U..,-

Please observe that there are special characters before the name, within the name and after the name.  The task is to remove special characters before and after the name.  The expected result is shown below:

Expected Result
Mohammed Zia-Ul Haque
Steven Thomas
Rohit Sunil Ahir-Chowdhary
Anuj
Sameer
Mohit
Rajeev Nair
Monalisa . Das
Vijeta
Anjana. M.U

The array formula (Ctrl+Shift+Enter) to make this work is

=MID(A2,MIN(SEARCH(CHAR(ROW($A$65:$A$90)),A2&CHAR(ROW($A$65:$A$90)))),LOOKUP(2,1/((CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))>=65)*(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))<=90)),ROW(INDIRECT("1:"&LEN(A2))))-(MIN(SEARCH(CHAR(ROW($A$65:$A$90)),A2&CHAR(ROW($A$65:$A$90)))))+1)

I have solved a similar problem at this link as well but that requires the usage of an add-in.  This is so because the special characters and numbers need to be removed from within the string as well.  In other words, everything except letters need to be removed from the alphanumeric string (no matter where the numbers and special characters are - beginning, middle or at the end).

Auto detect sum range when copying and pasting

{11 Comments}

Assume a simple two column data range as follows:

Product Amount
A 1
S 2
D 3
E 4
Product A
F 5
G 6
R 7
Product B
q 8
w 9
s 10
d 11
c 12
v 13
b 14
Product C

In the table above, one may want to compute the Product wise revenue.  Since the number of items falling in every product is different, one cannot copy and paste the SUM function from one product to another.  So one can adopt any one of the following two approaches:

1. Write the SUM function thrice; or
2. Use the short cut key for adding.  Here is the process (please note that this process will only work when there are no blank cells in the second column.  If there are blank cells, then the process mentioned below can be modified to still get the desired result):

a. Select range B2:B18
b. Press Ctrl+G > Special > Blanks > OK
c. Press Alt+= (this is the short cut key to generate the SUM function)

Now consider a different scenario.  Assume that the two column data range has the summarization row at the top of each Product block (rather than at the bottom as in the table above)

Product Amount
Product A
A 1
S 2
D 3
E 4
Product B
F 5
G 6
R 7
Product C
q 8
w 9
s 10
d 11
c 12
v 13
b 14

The second method described above will not work in this case because the Alt+= shortcut key only identifies thr range above or to the left.  To solve this problem, we need to write a formula for Product A which when copied down will auto adjust the range height.

You may refer to my solution in this workbook.  I have discussed two variants of the table above.

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

{11 Comments}

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:

1072.94
1643.02
1248.09
2167.34
276.85
322.05
910.22
1146.39
56.50

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.

Extract City, State and Pin code from an address string

{40 Comments}

For a lot of Excel users, a common problem is to extract City, State and Pin Code from an address string.  What compounds the issue is that there is no standardisation in an address string.  For e.g., one may end the address with a Pin code, while others may end it with a State and Country.  Some other variations could be:

1. End the address with Contact Numbers
2. There are no spaces between City, State and Pin code

There are of course many other variations which are possible and the sheer number of these variations makes it difficult to list down all of them.

Here is an example of address strings from where the State, City and Pin code need to extracted in three columns:

1. F-45, Pocket 1, Sector 198, Noida - 201303, Uttar Pradesh, India
2. S-45/B, Pocket 1-C, Phase II, Sector 189, Gurgaon, Haryana - 122002
3. RR-45/B, Pocket II-S, Cross 2, Noida - 201303, India
4. T-45, Sector 198, Lucknow - 226001, Uttar Pradesh, India
5. V-45(A), Sector 193C,Allahabad-211002 Uttar Pradesh India
6. V-45(A), Sector 193C,Allahabad-211002Uttar PradeshIndia

You may refer to my solution in 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.