excel challenge issued by my boss (help)

I am being asked to come up with a way to show the Top 5 projects based on value (source is a project database) that would automatically change each time data is entered or updated within the worksheet.  Top 5 should show the name of the project and the value of the project.  Honestly, I am not really a programmer but more like an average user of Excel and this challenge is really making my head spin for the past 3 hours.

Is there anyone out there who could help me?  Thanks in advance!

Hi,

You could use = LARGE(range of the values,1) and then LARGE(range of the values, 2) etc....

If the values would always be different then you could use VLOOKUP to get the name by referring to the value found in each LARGE, and getting the name that way.

HTH

Is the project database in Excel or an external data source?

I would simply add a column to the left of the data as you import it, and use the RANK function in this new column.  Then, in a separate area, have your summary of results that uses the VLOOKUP function for the name and dollar amount of your top 5 ranked projects.  The only potential complication is if you have two top-5 projects with EXACTLY the same dollar amount, inm which case you need to get a bit clever about how you deal with that situation.

Create a pivot table from the data, and use the Top 10 function (you can change it to top 5) to list just the top 5.

thanks guys for all your inputs... i shall try to apply each of your suggestions and see what would work for me :)

mattheq:  project database is within excel only to answer

bob: that scenario often happens as is with 2 similar projects :(

One trick is to covertly add $.001 to the first instance of a duplicate, $.002 to the second, etc. to fool the RANK function.  But it means you have to add one more interim step.

Cheers

Bob

To simplyfie bentleybob's suggestion you can have a look at this add-in: http://www.mrexcel.com/chal/pareto.zip

All of the formulas in B are =RAND() functions. Click the checkbox to recalc, and a whole series of dynamic range names cause the chart to redisplay sorted values.

It also make it possible to place the forulas for sorting the data in a defined name so youre WB will look like it is a built in function.
It is a beta version of a projekt that i never continued  to develop. so it doesent look very good but it does what it should. The add-in also allows
to create a calculated datatable that sorts the data as well as aoutomated creation of paretochart.