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 [...]

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. [...]

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 [...]

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 [...]

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, [...]

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 [...]

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 [...]

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 [...]

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 [...]

Read Full Post »