[duckspeak]

Apologies if this has been addressed already, but I've hit sort of a wall in my efforts. I'm relatively new to Excel. I know the basics and a few tricks. I use excel primarily for baseball stats and this is my problem:

Is there a reasonable way to automatically make a top-5 list according to the players' stats? For example, when the stat sheet is manually updated, a top-5 rank on another sheet is automatically updated. After Player A has outhit Player B, Player A moves ahead of B on the "top" list, and so on. I checked a bunch of autosort threads but they didn't appear to be like what I'm trying to do.

 

[RSpecianjr]

Hey duckspeak,

You can use formulas to do it, but to write the formulas we need more information.

Can you post the workbook on here? You just have to zip it first.

Regards,

Robert D. Specian Jr.

 

[duckspeak]

Certainly. The real work is on the "2011 Season" sheet. I drew up a manual ranking starting in column AC as an example of what I'm looking to have done automatically. The asterisks represent that the amount of next-best would spill off of the ranking.

 

[Corday]

In Excel F1:Type the word Large in the index. The explanation should allow you to create the formula you want. There is also an opposite called Small if you want the "bottomfeeders".

 

[duckspeak]

This is a step in the right direction, but I don't understand how I would correlate the name of the player to that rank and have just the name appear in the list.

 

[Corday]

Ah! Now I see. You need the player to "move" with the number. Hopefully I or RSpecianjr can figure this out. Not your normal Excel problem.

 

[JhonMoney]

in my opinion you should use large function. like large 1, large 2, large3 etc.

 

[RSpecianjr]

Hey,

I used array formulas with Large inside a Index formula. There is probably a better way to do it and I am eager to see someone elses method. I didn't know where you wanted it so I put it in the 2011 Season worksheet. It is easy to move to another worksheet etc. I also did it based on values in column H, I hope thats the right column, but wasn't sure. Here is the formula I used:

=INDEX(B:B,RIGHT(TEXT(LARGE(H$3:H$23+ROW(H$3:H$23)*0.001,AE22),".000"),LEN(TEXT(LARGE(H$3:H$23+ROW(H$3:H$23)*0.001,AE22),".000"))-FIND(".",LARGE(H$3:H$23+ROW(H$3:H$23)*0.001,AE22),1)),1)

AE22 is the place... in this case 1. I have also attached the workbook with the changes.

It would also be easy to add the corresponding value from column H.

Hope this helps,

Robert D. Specian Jr.

 

[duckspeak]

Robert,

I tried the formula with several different columns and every return I got was "Mike Napoli," the first name on the list. Even when I reinserted the formula on the copy you worked on, I got "Mike Napoli" instead of "Paul Janish" for number 1.

 

[RSpecianjr]

Hey duckspeak,

When you enter an array formula, you have to press CTRL+SHIFT+Enter instead of just enter or tab.

Ciao,

Robert D. Specian Jr.

 

[duckspeak]

Splendid! I have it now. Just one more thing. I notice that the formula returns 0 for the "AVG" column, as well as IP and basically all the numbers that are the result of a formula. Is there any way to tweak it so that it recognized those calculations as numbers?

 

[RSpecianjr]

Hey duckspeak,

Well, the formula is based on rows 3 to 23. Since you don't have any data for IP within those rows, it will show up as 0. I imagine the others are the same way. Blank cell = 0.

The formula works like this:

It takes all the values within the range H3 to H23 and adds their corresponding row in 100th place to it. Then it finds the largest value in that array of numbers and gives you the row it is in, based on the 100th place. With that, it will return the column B of the same row.

If you want to return a different column's value, change the B:B.
If you want to change the range, change the rows in H3:H23 to whatever rows you want.
If you want to change the criteria, change columns in H3:H23 to whatever columns you want.

Try putting something in the blank cells, you will see what is happening. If you need it changed, we are more than happy to adapt it however you want.

Regards,

Robert D. Specian Jr.

 

[duckspeak]

Yeah, that's what I was doing, but when I came across the "AVG" column, it returned a zero even though I had corrected all the column info. It seems to do it with any number involving a decimal or any number that was found by formula (h/g in the case of "AVG"). I've attached the book as it is now.

 

[RSpecianjr]

Right, the formula takes the 1000th place (sorry I said 100th before) and turns that into the row. If there is a decimal, it adds that as part of the row. If you want to be able to use this method, you will have to round the numbers with decimal places to some place. Doesn't have to be a whole number, but some of them have repeating remainders.

So, say you round to 100th place, you can then use a formula like:

=INDEX(B:B,RIGHT(TEXT(LARGE(P$24$43*100+ROW(P$24$43)*0.001,AC3),".000"),LEN(TEXT(LARGE(P$24$43*100+ROW(P$24$43)*0.001,AC3),".000"))-FIND(".",LARGE(P$24$43*100+ROW(P$24$43)*0.001,AC3),1)),1)

You can see where I added *100. It's odd that you have, particularly in IP, static values and formulas. If you take out the formulas and make them all static, it should work fine too. Go two decimal places and it should affect any of the other formulas results. This will also allow the above formula to work.

Regards,

Robert D. Specian Jr.