First Annual Excel Guru Challenge

Welcome to the First Annual Excel Guru Challenge.

Your task, if you decide to accept, is the following:

Use formulas (not VBA) to sort a list. Easy enough right? But to make it a little more interesting, the list has some duplicate values so you need to account for each one.

Please post an Excel file with your solution.

Let's see who will be crowned the Top Dog.

Good luck to you!

-- M

 

That's not enough info.

You should post the file to be sorted.

 

Here's the file to be sorted, with info on what the final sorted output should look like.

Have fun!

-- M

 

Whats in it for me?

 

Challenges are an excellent way to get others to do work for free.

You are in good company ultilizing a technique that every major company and software manufacturer also attempts to employ - through their lower level managers.

Good luck with your challenge.

 

This challenge is all for fun.

Feel free to participate if you're interested.

This challenge came about when I challenged my friends to come up with a better solution for a particular probelm than what I had developed.

After my buddy crushed my solution decisively, he offered this counter-challenge.

-- M

 

The first issue in the Challenge is to sort the % Change - not too hard using the LARGE function and incrementing the kth largest value as the row number increases.

To find the Stock Ticker that corresponds to that % Change is more challenging....

I used an array formula that played off the Small function which will find the kth smallest occurence of a value in a list. Unique occurences of a % change will have only one value, so the look up in a table of values is a simple Match function. The trick is how do you give Excel the "intelligence" to know how many occurences and to return each occurence of a duplicate "% change" . This is where the countif function comes in - combined with a half anchored range to only look up the the current row for duplicate occurences.

Excel Rocks!!!

dB Rocks Excel

 

dbTrader,

Very impressive solution.

How long did it take for your to bang this out?

I suspect it will be much less time than what it will take me to fully comprehend your solution!

U da Excel Man!!!!

-- MMM

P.S. As the current reigning Excel Champion, I think it'd be fair for you to pose the next Excel challenge, if you are so inclined.

 

Here's my buddy's version of the solution, and his thoughts on dbTraders answer to the challenge.


"For now, suffice to say his solution is more elegant than mine (the position #'s do not have to be in a separate column like mine
do), and I was unfamiliar with the large function, which I did separately. In fact his was quite clever with the use of nesting these functions in array formulas and which produced answers that were in the form of an array of numbers that uses the countif to choose the right position. That said, I'd probably replace the ROW(INDIRECT("1:20")) with ROW(INDIRECT("1:"&COUNTA($D$4:$D$24))) so that it can be made scaleable."