Tags: INDIRECT

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.

Generate a list of assignees for different projects based on a competency matrix

{0 Comments}

Here is a small sample of a Project matrix which shows tasks to be accomplished for various projects.  There can only be upto 6 tasks per project.

Project Name Task1 Task2 Task3 Task4 Task5 Task6
Project1 Painting Chef Gardener
Project2 Tiling Digging Engineering
Project3 Mechanic Engineering

Here is a competency matrix showing the competencies of employees on different tasks.  1 indicates that the employee is competent to perform that task.

Task Tom Jane Mary Paddy Lynda
Painting 1 1 1 1 1
Tiling 1 1 1 1 1
Plastering 1 1 1 1 1
Digging 1 0 1 1 1
Mechanic 1 1 1 0 1
Detective 1 1 1 1 1
Engineering 1 1 0 1 1
Boxer 1 0 1 1 1
Chef 1 1 1 1 1
Gardener 1 1 0 1 1
Banker 1 1 1 1 0

From these two tables, one may want to generate another table showing which employees can be assigned to which project (only those employees should be assigned to a project who can complete all tasks).  So the ideal solution is to create another column (8th column) in the Project matrix table above which should have a drop down (Data > Data Validation) for every project showing which employees are competent for that project.

Here's an illustration:

Assuming that the Project matrix is in range A1:G4 (headers are in row 1)

  1. In cell H2 (for Project1), the drop down should show Jane, Lynda, Paddy and Tom.  Mary should not appear there because she cannot perform one of the 3 tasks required to complete the project i.e. Gardener.
  2. In cell H3 (for Project2), the drop down should show Lynda, Paddy and Tom.  Jane and Mary should not appear there because they cannot perform the Digging and Engineering tasks respectively.

The solution is dynamic for the following:

  1. Projects added to the Project matrix Table; and
  2. Tasks added (upto 6 only) or edited in the Project matric Table; and
  3. Employees added to the Competency matrix Table; and
  4. Tasks added to the Competency matrix Table

I have solved this problem by using:

  1. Power Query; and
  2. Formulas in Data > Data Validation.

You may download my solution workbook from here or here.

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).

Compute potential Sales of a retail outlet

{8 Comments}

The objective is to assist a Store Manager with computing potential sales across different products and colours.  To start with let's assume two datasets:

1. Customer-Colour dataset - a two column table which lists down the colour preference of each customer; and
2. Colour-Product-Price dataset - a three column table which lists down the multiple products associated with each colour and the associated prices

My objective is to compute the "Potential Sales" if each customer buys one unit of each colour of each product.  While this can be resolved by using formulas (see Formula solution worksheet of the workbook), I'd like to resolve this problem with the help of the Power Query and PowerPivot tools.

The initial challenge will be to establish a relationship between the two tables because one cannot establish Many to Many relationships in a PowerPivot.  The two tables above are a perfect example of Many to Many relationships because each customer likes multiple colours and each colour is associated with multiple products.

You may download my solution workbook from this link.

You may also view a short video of my solution here:

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.

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.

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.

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.