Formula to Rank Values in Descending Order

**brent_excel** · 09-16-2015, 06:44 PM

Hello,

I am requesting your help...

In the attached workbook, I am in need of a formula that will accomplish 2 things:

1) Rank Units (column B) in descending order, based on department (column A).

AND

2) If there are duplicate values in the Units column, then look to the Sales (Column C) to decide the rank.

I have already accomplished the first part with the formula in Column D and the second part with the formula in Column E.

What I'm looking for is a single formula in Column F that will accomplish both of these things!

THANK YOU,

Brent

**Tony Valko** · 09-16-2015, 07:06 PM

Try this array formula** entered in D3 and copied down:

=SUM(IF(A$3:A$32=A3,IF(B$3:B$32>B3,1)))+SUMPRODUCT(--(A$3:A$32=A3),--(B3=B$3:B$32),--(C3<C$3:C$32))+1

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**Pete_UK** · 09-16-2015, 07:09 PM

There are lots of examples of ranking formulae on Chip Pearson's site, here:

http://www.cpearson.com/Excel/rank.aspx

Hope this helps.

Pete

**brent_excel** · 09-16-2015, 07:34 PM

Wow, Tony! That worked PERFECT. Exactly what I was looking for. That would have taken me forever to figure out. THANK YOU!

**Tony Valko** · 09-16-2015, 07:44 PM

You're welcome. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

Formula to Rank Values in Descending Order

LinkBack

Thread Tools

Rate This Thread

Display

Formula to Rank Values in Descending Order

Re: Formula to Rank Values in Descending Order

Re: Formula to Rank Values in Descending Order

Re: Formula to Rank Values in Descending Order

Re: Formula to Rank Values in Descending Order

Thread Information

Users Browsing this Thread

Similar Threads

Validate if values associated with the same part number are in descending order

Formula required to minus 2 units from 4 column values in the descending order..

[SOLVED] Rank values in descending order with duplicates

Sorting in descending order and show duplicate values

[SOLVED] Find and list corresponding values in a column in descending order

Rank amounts and duplicated in descending order

Rank Table in descending order

Bookmarks

Bookmarks

Posting Permissions