Tags: ISNUMBER

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.

Summarise data with multiple wildcard OR conditions

{0 Comments}

Imagine a dataset as shown below

Status Text Number
Active bat 1
Passive erq 2
Passive cat 3
Active enm 4
Active dog 5

Suppose the data is in range A3:C7 (headings are in A2:C2).  The objective is to add numbers where the following conditions are met:

1. Status should be Active; and
2. Text should contain either b or c or d

So the result for the dataset above should be 6 i.e. 1+5.  Sounding like a simple case of summarising based on multiple conditions, one may attempt using the SUMIFS() function here.  However, a single SUMIFS() function will not work because a SUMIFS() can only accept multiple AND conditions (not OR conditions).  The only way to make the SUMIFS() function work is to use 3 of them and then add them up.

So this SUMIFS() function will work

=SUMIFS($C$3:$C$7,$A$3:$A$7,A11,$B$3:$B$7,B11)+SUMIFS($C$3:$C$7,$A$3:$A$7,A12,$B$3:$B$7,B12)+SUMIFS($C$3:$C$7,$A$3:$A$7,A13,$B$3:$B$7,B13)

A11:A13 has Active and B11:B13 has *a*,*b*,*c*.

While this looks like a solution, the problem is that there will have to be one SUMIFS() function for each OR condition.  So the more the number of OR conditions, the more the SUMIFS() functions and the lengthier the formula.

In this workbook, I have shared two alternative solution.  One uses the DSUM() function and other uses a combination of MMULT(), TRANSPOSE() and SUMPRODUCT() functions.

Count entries in a range which exclude certain user defined words

{5 Comments}

Assume the following sentences in range B2:B6

B2 - This is an Apple Pie
B3 - An apple a day keeps a doctor away
B4 - These Pears, apples and mangoes are sweet
B5 - In this season, prices of mangoes have increased
B6 - This is a glass of Guava juice

In range B9:B10, type Mango and Apple.  The objective is to count the sentences in range B2:B6 which exclude the words mentioned in range B9:B10.

The array formula (Ctrl+Shift+Enter) for getting the count of exclusions would be:

=SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT($B$9:$B$10))),1*(ISERROR(SEARCH($B$9:$B$10,TRANSPOSE($B2:$B$6)))))=COUNTA($B$9:$B$10)))

The result should be 1.

The array formula (Ctrl+Shift+Enter) for getting the count of inclusions (sentences which include these words), would be:

=COUNTA($B$2:$B$6)-SUMPRODUCT(1*(MMULT(TRANSPOSE(1*(ISTEXT($B$9:$B$10))),1*(ISERROR(SEARCH($B$9:$B$10,TRANSPOSE($B2:$B$6)))))=COUNTA($B$9:$B$10)))

The result should be 4.

One can also solve the problem with the PowerPivot tool.  You may refer to my PowerPivot solution in this workbook.

Performing an iterative lookup to return closest match

{3 Comments}

Suppose a worksheet name is Code_details.  The following values lie in range A2:A14 of this sheet:

A12,AS478,QW447,EQ46,RYT9985,VCX147,BNM159,ASQ478,VC325,ASW675,A123,
ASDFG1234567890 and ASDFG123456789

Suppose there is another worksheet named Summary where the following values lie in range B5:B7:

ASQ47809876,ASW675458 and QWERT

Now one may want to lookup ASQ47809876 in A2:A14 of Code_details sheet.  If this exact value is not found there, then the lookup value should be trimmed by one digit/character from the extreme right i.e. the lookup value should now become ASQ4780987.  The search should carry on till an exact match is found.

You may refer to my solution in this workbook.

Analysing customer walkin data by date and service taken

{5 Comments}

Assume data is in range A3:E10.  In A4:A10, various service types are mentioned.  In B3:E3, dates are mentioned from June 1, 2012 to June 4, 2012.  In range B4:E10 are numbers representing number of customers.  One may want to answer the following questions from this data:

1. For every date, total number of customer walkins and total number of services taken; and
2. For every date, new customer walkins and new services taken; and
3. For every date, repeat customer walkins and repeat services taken

While the first and third questions are fairly straight forward to solve, some deliberation would be required for the second question.  A new service type taken on June 3, 2012 would be one that has not been taken by any customer from June 1 - 2, 2012.  So if cell A8 has Service type E and cell D8 (data for June 3, 2012) has 3 (3 customer took service type E on June 3, 2012), then this service should be counted only if there is no figure in range B8:C8 i.e. no customer took this service on June 1 - June 2, 2012.

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the Power Query add-in and the PowerPivot add-in, then a few simple steps and minimal DAX formulas can solve this problem.  The result will be dynamic and refreshable (just as in a Pivot Table).

Solution for all versions of MS Excel

While this solution works for all versions of MS Excel, it uses an array formula (Ctrl+Shift+Enter).  Array formulas, if used extensively in the workbook, adversely effect the system's performance.

You may refer to my solution in the this workbook.

Calculate turn around time excluding Sundays and public holidays

{188 Comments}

Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell).  Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays.

You may refer to my solution in this workbook.