Conditional Ranking in Excel

AndrewMcLaughlin
AndrewMcLaughlin used Ask the Experts™
on
Hi

I have a sheet of data, sheet A, that contains columns headers: OrgName, Subject, WiderGroupFlag, Score.

WiderGroupFlag is a Y or N depending on whether the organisation is a member of a wider group.

Now on a separate sheet, sheet B, I have the same structure but only the data that relates to one OrgName, the one I want to rank by score by subject amongst other OrgNames on Sheet A that are members of the wider group.

Is this doable?

Thanks,

A.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
Yes.  Can you provide a sanitized example workbook to demonstrate?

Let's assume column A has the organization, and column B has the value to be ranked, then the formula would look something like this (assuming data from row 1 to 100):

=SUMPRODUCT(($A$1:$A$100=A1)*(B1>$B$1:$B$100))+1

You could paste that, for example, in column C and copy down.

Multiple conditions can be tested for using this approach.

If you load an example I can help you implement, if more assistance is needed.


Dave
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
To rank the high score as 1, you might consider using an array-entered SUM formula like:
=1+SUM(--(VLOOKUP(A2,Sheet1!$A$2:$D$20,4,FALSE)<IF((Sheet1!$B$2:$B$20=B2)*(Sheet1!$C$2:$C$20=VLOOKUP(A2,Sheet1!$A$2:$D$20,3,FALSE)),Sheet1!$D$2:$D$20,-1E+30)))

Array-entered means hold the Control and Shift keys down, then hitting Enter. Excel should respond by putting curly braces { } surrounding your formula.

Brad
RankingWithCriteriaQ27729795.xlsx
Most Valuable Expert 2012
Top Expert 2012
Commented:
Borrowing heavily from Brad's spreadsheet & example, adding a column C for the score in Sheet2, expanding on my proposed formula, I would suggest:

=SUMPRODUCT((Sheet1!$C$2:$C$20="Y")*(Sheet1!$B$2:$B$20=Sheet2!B3)*(Sheet2!C3<Sheet1!$D$2:$D$20))+1

So, the first part - (Sheet1!$C$2:$C$20="Y")  ensures we're only looking at the wider group

the next part - (Sheet1!$B$2:$B$20=Sheet2!B3) ensures we're only ranking within the same subject, and

the last part - (Sheet2!C3<Sheet1!$D$2:$D$20) does the comparison for ranking and +1 to start from 1 as the highest score in the ranking process.

Cheers,

Dave
RankingWithCriteriaQ27729795-w-S.xlsx
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Most Valuable Expert 2012
Top Expert 2012

Commented:
Brad, I hope we meet someday.  I'd like to buy you a pint.  In fact, I'm going home to my family and will pop one open and cheers to you.

Nite all.

Cheers,

Dave

Author

Commented:
Hi Brad/Dave,


When I enter this formula as an array I get a Name error - an ideas why.

=SUMPRODUCT((RG="Y")*(subject=$B3)*($C3<MEDIAN))+1

Thanks,

Andrew
Most Valuable Expert 2012
Top Expert 2012

Commented:
There is no need to enter that SUMPRODUCT as an array.  SUMPRODUCT already is an array function.

MEDIAN is an Excel function, so you might want to rename that, however, you're probably getting the NAME error because RG or Subject or MEDIAN is not defined.  Please check to see that they are defined in your name manager.

Can you upload a sample, if more help is needed?

Dave

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial