Which Group does a name belong to?

Consider this problem

The table above gives the groups in which each of the members belong. the problem here is to find out in which group one of the member belongs to e.g. Keith (A12). The formula used in B12 is

=INDEX($A$1:$C$1,1,SUMPRODUCT(- –ISNUMBER(SEARCH(A12,$A$2:$C$8)),{1,2,3}*{1;1;1;1;1;1;1}))

The way this works is

• Search function searches for Keith (A12) in the array A2:C8. It returns #VALUE! for cells where Keith is not present and 1 for the cell in which Keith is present
• Isnumber returns True for the cell in which Keith is present and False for the rest
• Operator – – changes True and False to numbers 1 and 0 respectively
• Thus we have an array of 0s and one 1 with 7 rows and 3 columns
• We now generate an array with {1,2,3}*{1;1;1;1;1;1;1}. This array is of the form

• Sumproduct multiplies the earlier generated array of 0 and 1 with our array such that it returns the column number of cell in which Keith is present – 2 in this case
• Index simply returns the equivalent group from the header array A1:C1 – Lions in this case.

Can you figure out a way of not putting the array manually by using the function row and column functions.

Read Full Post »

Lookup for “contains”

Here is a method for looking up “contains” from the lookup value array.
Consider the problem below. I have sales figures for Asia Pacific countries. The lookup value extracted from the system is in the format “Country City”

The expected results are given in the table D1:E11.

Here is the solution using search and sumproduct. You may use array function instead of sumproduct. Suit yourself.

E2=SUMPRODUCT(––ISNUMBER(SEARCH(D2,CountryCity)),Sales)

where, CountryCity is A2:A14 and Sales is B2:B14

You do not need to change the formula while computing for a country figure. The same formula works perfectly.

Now, how does this formula work,

• Search function looks for the lookup city or country in all cells of CountryCity. (You could use Find function but it is case-sensitive). For cells which contains the lookup city or country it returns a number
• Isnumber converts this to True for cells containing the lookup value. Rest are False
• The operator –– converts True to 1 and False to 0 (Refer earlier post)
• Sumproduct multiplies the array of 1’s and 0’s with the Sales array and returns the summation of Sales figure where the “contains” for lookup value is True

Have a great weekend 🙂

Read Full Post »

Cell value from a grid

While we can use vlookup or hlookup to return a cell value in a particular row or column. Is there a way we can lookup from both row and column to return a particular value from a cell in a grid?

Consider the case below:

(Yes, I have added more colours to the table this time 🙂 )

The construct is such that every week 5 batches of measurement is carried out. The dates are given in cells A2:A14 and batch numbers are given in cells B1:F1.

Result table for measurement for specific dates and batches is given in the table below:

You could use one of the following formulas to arrive at the result:

Method1: vlookup with match

C17=VLOOKUP(A17,A2:F14,MATCH(B17,A1:F1,0),FALSE)

Of course, you could find the same result with hlookup with match. Only remember to lookup in a row instead of a column

Method2: Index with match

C18=INDEX(B2:F14,MATCH(A18,A2:A14,0),MATCH(B18,B1:F1,0))

Method3: Indirect with address and match

C19=INDIRECT(ADDRESS(MATCH(A19,A2:A14,0)+ROW(A2)-1,MATCH(B19,B1:F1,0)+COLUMN(B1)-1))

And now the bonus formula – using array

Method4: Sum as an array function

C20 {=SUM(($A$2:$A$14=A20)*($B$1:$F$1=B20)*B2:F14)}

Array formula is entered with ctrl+shift+enter

Method5: Use sumproduct as an alternate for array function

C21=SUMPRODUCT(($A$2:$A$14=A21)*($B$1:$F$1=B21)*B2:F14)

Use one of the above formulas or make one for yourself and share with others. Have fun 🙂

Read Full Post »

Extracting the nth occurence from an array

In one of the earlier posts, I extracted the first, last, min and max occurence from an array for a particular lookup. Refer this post.

The next step perhaps would be to answer if it is possible to extract the second, third occurences and so on. Consequently to extract all occurences for a particular lookup_value.

Consider the same example, as earlier:

Each of the 4 persons randomly appear for a test and their scores are recorded. I had given methods to extract the scores for 1st and last attempt by each of the 4 perons. Now lets find out their scores for 2nd, 3rd, etc attempts

The table above gives the results. Row 1 gives the number of attempt.

The formula used for getting to the result is

F2=IF(COUNTIF(Person,$E2)>=F$1,INDIRECT(ADDRESS(SUMPRODUCT(LARGE((Person=$E2)*ROW($B$2:$B$15),COUNTIF(Person,$E2)+1-F$1)),3)),”-“)

Person is B2:B15

As you would see, I have used sumproduct to avoid using array formula and a combination of indirect and address to extract results.

Here are the steps to understand this formula –

• Lets begin with the component LARGE((Person=$E2)*ROW($B$2:$B$15),COUNTIF(Person,$E2)+1-F$1
• Remember Large function has the syntax LARGE(array,k) which returns the kth largest value from the array. For example if k=2, then the function returns second largest value in the array.
• Now, (Person=$E2)*ROW($B$2:$B$15) would create an array like {0,2,0,0,5,0,0,0,9,0,0,0,0,0,0} which is essentially the row numbers of cells in the array where Person=Mike is true
• For the k part in large function, I have entered COUNTIF(Person,$E2)+1-F$1. Mike occurs 3 times in array Person, so the k would be 3,2,1, such that 1st attempt score is followed by 2nd attempt score and so on
• Once this is clear, rest is easy
• Address function has syntax Address(row_num, column_num, [abs_num]). row_num is given from the large function, column_num is entered as 3, which is the column number of Score, [abs_num] is omitted which returns address in absolute eg $C$2
• Indirect(“$C$2”) returns the value inC2.
• If condition at the beginning simply evaluates if we have exhausted all occurences of Mike. Once done, it returns a “-“.

§ Note: This also is a better way for multiple extraction because sorting of original table is not required. 

There are 3 other alternative formulas given in the earlier post to achieve the same result.

Else, if one method is sufficient for you, use this and have fun 🙂

Read Full Post »

First, Last, Min, Max for a particular value in an array

Consider a set-up where 4 users randomly appear for a test. Their scores are recorded in the order in which they appear for the test. Our objective is to find their scores in the first and last attempts and also their minimum and maximum scores. Here are the users and their scores:

The objective is to find the first, last, min and max score for each user. The solution looks like this:

For first attempt score, the formula is a simple vlookup

F2=VLOOKUP(E2,$B$2:$C$15,2,FALSE)

Person is B2:B15 and Score is C2:C15

For max score, the formula is simply:

H2 {=MAX(IF(Person=E2,Score,0))} entered as an array formula with ctrl+shift+enter

Similarly, for min score, the formula is

I2 {=MIN(IF(Person=E2,Score,100))} entered as an array formula with ctrl+shift+enter

I have entered 100 as it is greater than the largest number in Score. You may replace this with max(Score)+1.

Now for last attempt score, you could use one of the 3 sets of formulas

1. Using Index function – array formula

G2 {=INDEX(Score,MAX((Person=E2)*ROW(Person))-ROW(Person)+1)} entered as an array formula with ctrl+shift+enter

In this formula, max function finds out the last row number for a particular user. For instance for user Andy, MAX((Person=E2)*ROW(Person)) returns 14. To return the score for Andy in the last attempt, I have used index function. The minus part of ROW(Person)+1 simply takes care of cells above array Score.

2. Indirect-address function – array formula

G2 {=INDIRECT(ADDRESS(MAX((Person=E2)*ROW(Person)),3))} entered as an array formula with ctrl+shift+enter

If you feel uncomfortable with entering a number in the formula, you could replace 3 with column(Score)

3. Index – Sumproduct function

If you are tired of using array formulas, sumproduct offers some respite

G2=INDEX(Score,SUMPRODUCT(MAX((Person=E2)*ROW(Person)))-ROW(Person)+1)

The formula is similar to the Index with array formula. Here sumproduct is used differently then the normal usage. Notice that there is only one array in sumproduct. The function treats the parameters entered as an array, so we need not enter it as an array formula.

Similarly, indirect address formula can also be entered with sumproduct without entering it as an array formula.

Give it a try. Have fun 🙂

Answer:

Read Full Post »

Evaluating step functions

This problem is on step functions. Certain utility company charges me in this manner

For the first 25 units of consumption, the rate is 212 per unit, for next 35 units, the rate is 270 per unit and so on.

I have calculated the total amount payable for certain unit of consumption by using sumproduct and offset functions.

The formula to calculate amount payable is entered in C11 for consumption in B11

C11 =SUMPRODUCT(OFFSET($B$1,1,0,MATCH(B11,Cum_unit,1)),OFFSET($B$1,1,1,MATCH(B11,Cum_unit,1)))+(B11-INDEX(Cum_unit,MATCH(B11,Cum_unit,1)))*INDEX(Rate,MATCH(B11,Cum_unit,1))

The formula can be divided in two parts, the part in blue calculates the amount upto the upto the completed values given in cummulative units (Cum_unit) – 100 in the given example. Cost for remaining units (120-100=20) is calculated in the second part (formula given in green)

Cum_unit is D2:D8

sumproduct – sums up the products of two arrays [sumproduct({1,2},{3,4})=1*3+2*4=11]. This is exactly what we need to calculate the first part – {Units}*{Rate}

To find out how much of the array we need to sumproduct, match function is used. Match with match_type 1 is used for array Cum_unit, so that it returns a value equal to or lesser than the lookup_value. In our case it returns the value 3

Offset is used to generate the arrays for carrying out sumproduct, the reference is taken as B1, for Units array, row offset is 1, column offset is 0, height of array is given by the match function above – which is 3

For Rate array, row offset is 1, column offset is 1, height is same as given by match function.

The cost for remaining part is carried out through the use of index function on the two array Cum_unit and Rate and multiplying them

You would find this very useful when working on large units and rates arrays.

As always let me know if you find this useful 🙂

Read Full Post »

Lookup to the left

For looking up through vlookup, the value to be returned needs to be to the right of lookup_value. Here is a solution for returning a value to the left of lookup_value using match and offset functions

The problem is given in the image sheet below

 

The H1Y1 performance of certain sales people have to be returned. This is achieved through this formula in cell B10

Method 1: Match with Offset

B10 = OFFSET($C$2,MATCH(A10,$C$3:$C$7,0),-2)

This is then dragged to cover all sales people for which figures are required.

Note: match function returns the placement of lookup_value in the column, this is then used to offset. Note that offset reference (C2) is one cell above the lookup column (C3:C7) to ensure that if match returns 1, offset gives a correct value

Method 2: Sum as an array function

B10 {=SUM(($C$3:$C$7=A10)*$A$3:$A$7)}

entered with ctrl+shift+enter

Method 3: Sumproduct

B10=SUMPRODUCT(–($C$3:$C$7=A10),$A$3:$A$7)

May you find this useful!

Read Full Post »