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 »

Text to Numbers

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

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 a 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

 

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 »

Cows and Bulls

Here is how you can play the game Cows and Bulls in excel

The objective of the game is to guess the secret number (which is hidden from the player). The player can guess the number as many times. If the matching digits are on their right positions, they are “bulls”, if on different positions, they are “cows”. Example given below

The formula for cows:
C3 {=COUNT(MATCH(MID(B3,ROW($1:$4),1),MID($B$1,ROW($1:$4),1),0))}
(entered as an array formula, ctrl+shift+enter)

The formula for bulls
D3 {=SUM(IF(MID($B$1,ROW($1:$4),1)=MID(B3,ROW($1:$4),1),1,0))}
(entered as an array formula, ctrl+shift+enter)

Drag the formula down. You could keep entering your guesses in column B

One could also highlight the cell if the number is guessed correctly, through conditional formatting
Click B3 and through Format>Conditional Formatting…

Copy the format to all cells in column B through Format Painter brush on the toolbar

Tip: If you are playing the game alone and have no one to give you a 4 digit number, you could use rand() function

In the secret number cell enter = ROUND(RAND()*10^4,0), Press F2 and F9 – this would freeze the random number. Make sure that no digits are repeated and the random number is 4 digits long. You can use len function and check duplicate function for that

Have fun playing the game 🙂

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 »

Unraveling a table

Here is the problem that I faced

 

 

 

 

I had to extract the entries in the table row-wise and populate this in a single column.
Here is one way of doing this:

• Enter A8=OFFSET($A$2,INT((ROW()-ROW($B$8))/5),MOD((ROW()-ROW$B$8)),5),1,1)
• Drag down till the entire table is unravelled.

What if we had to unravel the table column wise i.e. in the order A2, A3, A4 etc

Here is the formula that you need to enter in B8 and drag till the last table entry

B8=OFFSET($A$2,MOD((ROW()-ROW($B$8)),3),INT((ROW()-ROW($B$8))/3),1,1)

One could also extract the table in a columnar format. We would have to switch row for column to do so.

mod has the syntax mod(number, divisor) and returns the remainder when number is divided by the divisor. In the above case when the divisor is 3, it returns 0,1,2,01,2 and so on

int(number) returns the nearest integer less than or equal to the number. In the above case int returns 0,0,0,1,1,1,2,2,2 and so on

This is exactly what we need to offset from the reference in the table and unravel it.

Note the divisor in the two formulas, in the first formula the divisor is 5, which is equal to number of columns in the table

In the second formula divisor is 3, which is equal to number of rows in the table

This could also be computed through columns(A2:E4) and rows(A2:E4) respectively

Let me know if you ever find this tip useful or are not clear about any part of the post.

In the meantime, have fun working with excel 🙂

Read Full Post »

Generating a series of numbers

In some of my earlier posts, I have manually populated some cells with a series of numbers (numbers 1-4 were manually populated in the post multiple extraction through vlookup). Here is a way we can do away with this by using functions row() and column()

Lets say you want to populate 0 to 10 in a column in cells B5 through L5:
Enter B5=COLUMN()-COLUMN($B$5)

On dragging upto L5, it populates the cells with the series 0 – 10.

As you can figure out

• column() returns the column number of that particular cell
• If a reference is provided [eg. column(B5)] – it returns the column number of the referred cell – 2 in the above example

If we were to populate numbers in a row, the function row() could have been used. To populate cells A1 to A11 with numbers 0 to 10,

• A1 = row()-row($A$1) – drag till cell A11

’tis done!

§ I am going to leave an example for you to think about the utility of this. Also would use this function in my next post.

√ To sum first 10 natural numbers – 1 to 10 i.e Σ(1,2..10)

Enter this in any cell =SUM(ROW(A1:A10)) as an array formula i.e. ctrl+shift+enter instead of enter. This gives the result as 55

The formula is interpreted as SUM({1;2;3;4;5;6;7;8;9;10})

If you found this interesting or would like some more help on this, let me know 🙂

Read Full Post »

Reversing order of a table

I had saved monthly data by adding months on top of the heap (as in table)

Now while presenting this data, I wanted the data to appear in chronological order, January in row 1, February in row 2 and so on.

 

 

I achieved this by using the offset function.
In cell F3 enter the formula =OFFSET(B$3,8-ROW(),0,1,1)

Drag the formula to the right and down to create a new table.

Here constant 8 is the row number of the last row in the source table. This would work only when the first data row is at the same level as the first data row in the source table (F3 is in the same row as B3)

In practice though if constant is the last row number of the destination table, it would work even when the first rows of source and destination tables are at different level

Read Full Post »