Feeds:
Posts
Comments

Posts Tagged ‘indirect’

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:

tip35-cell-in-a-grid

(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:

tip35-cell-in-a-grid_result

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 »

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:

tip32-1st-last-min-max

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

tip34-nth-occurence

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 »

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:
tip32-1st-last-min-max

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

tip32-solution

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:

tip32-quiz

Read Full Post »

Just like that, each alphabet corresponds to a number, summing up alphabets

I cant really see how this post could be useful to you. One of my friends who believed in numerology brought this problem to me of converting names to numbers. Here is the solution that we worked out. You may treat this as a Just Like That entry until it finds any use.

Lets say the names are stored in column A, have worked out a numerical equivalent in column B using the equivalence (A or a =1) and (Z or z = 26) with all other alphabets in between

tip29-text_to_numbers

Method 1: Using CODE function

For windows CODE(text) returns the ANSI code of the first character in the text string.

  • code(“A”)=65 and code(“Z”)=90 with all other upper case alphabets in between
  • code(“a”)=97 and code(“z”)=122 with all other lower case alphabets in between

The formula in B2 {=SUM(CODE(MID(LOWER(A2),ROW(INDIRECT(“1:”&LEN(A2))),1))-96)}

entered as an array formula with ctrl+shift+enter

Here lower function is used so that all alphabets are treated as lower case and ansi code is from 97 to 122

Hence, 96 is subtracted so that has numerical equivalent of 1

Cell A4 (Anup Agarwal) has space between the first name and last name. We can use substitute function to replace the space with no space. The revised formula in cell B4 is

B4 {=SUM(CODE(MID(LOWER(SUBSTITUTE(A4,” “,””)),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A4,” “,””)))),1))-96)}

again entered as an array formula with ctrl+shift+enter

Method 2: Using lookup function

You can avoid using CODE function and use the lookup function to achieve the same result. Have defined the following table as equivalent

tip29-text_to_numbers_equivalent

 

The formula entered in B2  {=SUM(LOOKUP(MID(A2,ROW(INDIRECT(“1:”&LEN(A2))),1),equivalent))}

entered as an array formula with ctrl+shift+enter

The formula entered in B4 is

{=SUM(LOOKUP(MID(SUBSTITUTE(A4,” “,””),ROW(INDIRECT(“1:”&LEN(SUBSTITUTE(A4,” “,””)))),1),equivalent))}

entered as an array formula with ctrl+shift+enter

Have fun and let me know if you ever find use of it 🙂

Read Full Post »

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 thistip15-insertspace-2

 

 

 

 

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

tip15-insertspace

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 »

This is a very interesting problem that we encountered.
To simplify the problem, the objective of the exercise was to insert spaces within a continuous list. For example

 

tip5-insertspaceColumn A has the original list and column D has the desired result. Two blank cells needed to be inserted between the entries.
To solve this problem, we made use of Indirect function.

Indirect is a lookup function that returns the reference specified by a text string. In the example above

Indirect(B1) returns “cherry” – cell B1 has A1 which contains cherry

and Indirect(B2) returns “microsoft” – cell B2 has A5 which contains microsoft

Here is an interesting variation:

Indirect(“B1”) returns A1 and Indirect(“B2”) returns A5

To solve the original problem

C1=1

C2=C1+1/3 (on dragging, this will insert two fractional numbers between the whole numbers)

D1=IF(ISERROR(INDIRECT(“A”&C1)),””,INDIRECT(“A”&C1))

The indirect function here takes the references as = indirect(“A1”)

You get the desired result when you drag the formulas

It works!

Read Full Post »