Link to home
Start Free TrialLog in
Avatar of AndrewMcLaughlin
AndrewMcLaughlin

asked on

Conditional Ranking in Excel

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.
Avatar of dlmille
dlmille
Flag of United States of America image

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
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of AndrewMcLaughlin
AndrewMcLaughlin

ASKER

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
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