Posts Tagged ‘function index’

Excel and palindromes (continuation)

January 10th, 2010

Palindrome list

In my last post about Excel and palindromes I showed you how to check if a sentence is a palindrome. Today, based on the list of different sentences (including palindromes) we will prepare an automatically generated list containing only palindromes.

Excel and list of palindromes

We will use a helper column (column F in my case). Let’s select the amount of cells in our helper column to equal the number of sentences in our source list. In my case it’s 7 cells.

Excel and palindromes
Next, let’s type in the following array formula:

{=SMALL(IF($D$8:$D$14,ROW($D$8:$D$14),""),ROW(INDIRECT("1:"&COUNT(IF($D$8:$D$14,ROW($D$8:$D$14),"")))))}

The formula uses the “is palindrome?” column ($D$8:$D$14) we talked about last time. For each found palindrome it returns the palindrome’s row number, and the error value #N/A otherwise.

What’s left now is to type the following formula into the first cell of my palindrome’s list:

=IF(ISERROR(INDIRECT(ADDRESS(F8,2))),"",INDIRECT(ADDRESS(F8,2)))

We could alternatively use a simpler formula:

=IF(ISERROR(F8),"",INDEX($B:$B,F8))

and copy it down to the cells below. Should work.

Marcin

Creating a bestseller (Top 10) list with Excel

December 7th, 2009

Today we are going to create a bestseller (top 10) list. As a source material we will use a list of products with a corresponding number of products sold within a chosen period of time.

What we want at the end is to generate a list of top 10 selling products. To make it slightly more difficult we want this list to automatically update every time the number of products sold changes, and – just for fun – we don’t want to use VBA macros.

Creating a bestseller (top 10) list in Excel

First, let’s sort (descending order) all the sale figures and choose 10 best ones. To do so I decided to use the function LARGE and an array formula entered into the range I7:I16 spanning over 10 Excel cells. Our array formula looks as follows:

=LARGE(Sheet1!C4:C19,ROW(INDIRECT("1:"&ROWS(Sheet1!C4:C19))))

Where C4:C19 is a range with our sold products amounts.

Creating a bestseller (top 10) list in Excel

As a result we get a list of top 10 sales.

Creating a bestseller list in Excel
Now, the more difficult part. How to assign product names to the numbers ?

If we were sure that the numbers of sold products will never be the same (we don’t have any recurrent values) we could simply use INDEX and MATCH functions to match the appropriate product name to the number. Our formula could look like this:

=INDEX(Sheet1!B4:B15,MATCH(C6,Sheet1!C4:C15,0),1)

And it should work fine. But, if the sale amounts repeat, the above formula will return the same product name for each repeating number.

Excel and a bestseller list

That is not what we want. That’s why I decided to use a little different approach. To get the first product name I used the formula:

=INDEX(Sheet1!B4:B19,MATCH(F6,Sheet1!C4:C19,0),1)

And for the following product names, an array formula

=INDIRECT("Sheet1!"&ADDRESS(SMALL(IF(Sheet1!$C$4:$C$19=F7,ROW(Sheet1!$C$4:$C$19),65536),COUNTIF($F$6:F7,F7)),2))

inserted into one cell and copied over the remaining 8 cells.

Excel and a bestseller list

As you can see on the animation in the beginning of this post, the solution seems to work just fine. Every change in the sold products amount, results automatically in an adjusting change on our TOP 10 list. Enjoy!

Marcin