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 »

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 »

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 »

Insert spaces – revisited

I had earlier posted a solution on inserting spaces between continuous cells in a list. Though the solution works fine for a few items (46 items in the earlier example), it returns a blank for items after that. The solution had comprised of using the formula C1+1/3; where C1=1 and dragging it so that two fractional numbers are inserted between the integers i.e. 1, 1¹⁄3, 1²/3, 2 and so on. Excel processes these as recurring decimals and possibly rounds off recurring decimals. So the rounding off errors gets added up to the point where it is large enough for indirect function to fail.

Have a look at this

 

 

 

 

Where F2=INDIRECT(“A”&E2)

The indirect formula continues to work as long as the error ε<1E-15. Any bigger error returns a function error.

Have solved this problem through using a combination of int and mod, which works well for very large lists as well

The original list is given in array A2:A9 (which has also been defined as Original_list).

Enter B2=IF(MOD((ROW()-ROW($B$2)),3)=0,INDEX(Original_list,INT((ROW()-ROW($B$2))/3)+1),””)

Drag till the entire list is mapped

You could change the constant 3 to a higher number if you need to insert more spaces between the entries

Let me know if you find this useful 🙂

Read Full Post »

Multiple extraction through vlookup

The problem here is extraction of multiple results from a two column array through a vlookup

 

The data available is given in columns B and D. The 4 regions along with their major cities are known. The data needs to be arranged as in the table below

Start by sorting on region (column B)

Column A is populated with serial numbers such that each region has a fresh series

Cell A3=1, Cell A4=IF(B4=B3,A3+1,1) – drag till end of column

Column C is concatenation of columns B and A

Cell C3=CONCATENATE(B3,A3) – drag till end of column

This enables us to have a one on one mapping with region and cities

Now, to generate the result table on the right

Cells – G1:J1 is manually populated with numbers 1 through 4

Cell G2=VLOOKUP(CONCATENATE($F2,G$1),$C$3:$D$18,2,FALSE) – drag till end of column and end of row

’tis done!

Alternative solution 1 using offset

To generate the table in F11:G14

Populate F11 to F14 with East, West, North, South

Here again, we would have to sort on region (column B) first

Enter G11 = IF((COLUMN()-COLUMN($G$1))<COUNTIF($B$3:$B$18,$F11),OFFSET($D$3,MATCH($F11,$B$3:$B$18,0)-1+(COLUMN()-COLUMN($G$1)),0,1,1),””)

Here,

column()-column($G$1) generates numbers 0,1 etc

countif – returns number of occurences of east, west, etc

offset – offsets from the reference cell D3, number of rows given by first occurence of east – provided by match function + serial number given by the column function

You could seperate each of the functions to understand how this works. If you need any help in understanding do let me know

 

Drag to the right and below to populate the entire table

’tis done again

Alternative solution 2 using index

To generate the table in F18:G21

Populate F18 to G21 with East, West, North, South

Enter G18 =IF((COLUMN()-COLUMN($G$1))<COUNTIF($B$3:$B$18,$F11),INDEX($D$3:$D$18,MATCH($F11,$B$3:$B$18,0)+(COLUMN()-COLUMN($G$1))))

Drag to the right and down to populate the entire table.

’tis done once again

Read Full Post »