Create a ranking column

**tlacloche** · 07-09-2014, 01:42 PM

Hello,

Please see the attached workbook.

can you please make the purple area be a ranking system based on the cities' scores in the orange section.
As more cities and scores are added in the orange section, the purple section should update automatically.

Thank you

**Ron Coderre** · 07-09-2014, 02:27 PM

Using your posted workbook
First...convert the City/Score list to an Excel Table so it will automatically expand/collapse to accommodate the table contents when you add/delete table rows.
• Select D5:E14
• Home.Format-as-table...follow the prompts.
• Rename the table to MyTable (so my formulas will work for you)
...Select a cell in the table.
...Table_Tools.Design.Table_Name: MyTable

Now...in the RANKING section
For a single-formula approach:
This formula begins the list of cities in descending order of Score:

Please Login or Register  to view this content.

And this formula returns the score for that city:

Please Login or Register  to view this content.

Copy both formulas down as far as you need.

OR...using a helper cell with the MyTable table
• Type this in cell G5: RANK
...Excel will automatically expand the table to include that column
• This formula will assign a rank to each row

Please Login or Register  to view this content.

(You'll notice that as soon as you finish the formula the Excel Table copies is down the column for you)

Now, use these formulas to list the rankings in order:

Please Login or Register  to view this content.

Copy those formulas down as far as you need.

Using either method...these will be the results:

Please Login or Register  to view this content.

Is that something you can work with?

**tlacloche** · 07-09-2014, 02:35 PM

thanks.

however, i have a feeling that it is possible without the need for tables. The range will always be D6:E34. Whether all rows will be occupied or not is a different story.

also is there a way without a helper column?
surely theres a formula that can be put in columns K and L to give the desired result.

i didnt think it would be so complicated.

thanks

**hoyasaxa215** · 07-09-2014, 03:12 PM

It's more complicated than you think, especially because you can have tie scores. It's probably easier just to copy the entire list into the rankings columns and than just sort by score.

**micope21** · 07-10-2014, 06:37 AM

Hi

L16

Formula:

Please Login or Register  to view this content.

Copy down.

K16

Formula:

Please Login or Register  to view this content.

This is array formula, Press same time Ctrl+Shift+Enter, NOT ENTER! Then copy down

Regard
micope21

**tlacloche** · 07-10-2014, 09:00 AM

LEGEND!!!!!

Thank you so much micope.
i had a feeling it was possible.

youre a star!!!

**tlacloche** · 07-10-2014, 09:14 AM

one last thing...

my data results in many "0" to be visible in column L.
can you please adjust the

Formula:

Please Login or Register  to view this content.

to replace "0" outcomes with blanks.

thank you

**micope21** · 07-10-2014, 09:35 AM

Hi

If you wantt 0? See this: ROW($1;1),"") change to this: ROW($1:1),0).

Regard
micope21

**tlacloche** · 07-10-2014, 09:39 AM

i dont want to see "0".
instead i want the cell to be blank if there is a "0"

thanks

**micope21** · 07-10-2014, 09:47 AM

Hi

Should be blank? if you enter 0 next to name then yes it will show up 0 in Ranking order?

**tlacloche** · 07-10-2014, 09:51 AM

the rows that im pulling the ranking data from has already got formulas in them. half of the formulas have a city associated with it (and so are displayed perfectly)
the other half, does not yet have a city (its currently N/A). so your ranking formula is still taking the "0" from the rows that dont have a city yet therefore resulting in "0" in the ranking column.

i was hoping that a tweak can be done to the ranking formula to simply display "0" cells as blanks so we dont have to see them

**tlacloche** · 07-10-2014, 09:53 AM

in other words is there a

Formula:

Please Login or Register  to view this content.

version for zeros

**micope21** · 07-10-2014, 09:58 AM

This formula will give you a blank instead showing up 0?

L16

Formula:

Please Login or Register  to view this content.

copy down

Regard
micope21

**tlacloche** · 07-10-2014, 10:00 AM

thank you
thank you
thank you

**micope21** · 07-10-2014, 10:04 AM

You welcome!!!

Thank you for Reputation!

Create a ranking column

LinkBack

Thread Tools

Rate This Thread

Display

Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Re: Create a ranking column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Ranking up to 15 numbers in column D Ranking skips 7 with a tie at 6 and 2

[SOLVED] Ranking numbers in a column from 1 to 15 then stating the rank in another column

[SOLVED] How do I create a unique ranking from an array/table of values?

[SOLVED] Create an automatically sorting/ranking list

ranking the values in one column that share the same value in another column help!

Bookmarks

Bookmarks

Posting Permissions