Chris Stormer
asked on
Use Rank With Prerequisits in Excel
Building on a previous question I had where I wanted to rank the results in excel here:
https://www.experts-exchange.com/questions/29220084/Sort-Score-A-Range-of-Values-in-Excel.html#questionAdd
I am not interested in ranking based on a group. In my example I am calling this Class so what is the rank of a value for that class of numbers.
https://docs.google.com/spreadsheets/d/1PeStssumF6D1LrJG1Nhz91QPctYR4lALUUpVZWR6_Vc/edit#gid=0
Is there a way to have the ranking be for a group of values?
https://www.experts-exchange.com/questions/29220084/Sort-Score-A-Range-of-Values-in-Excel.html#questionAdd
I am not interested in ranking based on a group. In my example I am calling this Class so what is the rank of a value for that class of numbers.
https://docs.google.com/spreadsheets/d/1PeStssumF6D1LrJG1Nhz91QPctYR4lALUUpVZWR6_Vc/edit#gid=0
Is there a way to have the ranking be for a group of values?
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
The attached file is working in Excel (Microsoft 365).
I couldn't use the RANK & FILTER formula shown above in Excel (though it did work in Google Sheets). As a workaround, you may use either of:
Ranking values with dependent criteria.xlsx
I couldn't use the RANK & FILTER formula shown above in Excel (though it did work in Google Sheets). As a workaround, you may use either of:
=1+SUMPRODUCT((C$2:C$7<C2)*(A$2:A$7=A2))
=1+COUNTIFS(C$2:C$7,"<" & C2,A$2:A$7,A2)
Ranking values with dependent criteria.xlsx
ASKER
Thanks so much Byundt. Will this all still work this way if what is in column C a word instead of a number.
For example instead of 5, I have the word technology?
For example instead of 5, I have the word technology?
RANK only works with numbers. SUMPRODUCT and COUNTIFS will work with text, but will alphabetize rather than rank based on word count.
You probably need to post another workbook with sample data and expected results. The data ought to include two different criteria values, duplicate values for at least one of column C value and column A criteria. And for sure you need to post the desired results.
ASKER
I should have started with the most complex version that I was trying to get to but I figured if you guys got me started I would be able to figure it out.
I've rewritten the question to be more clear.
https://docs.google.com/spreadsheets/d/10YFyzc9EymEYsMkIDKNG90eFOGao2HxGOp2hlV3IdW8/edit#gid=0
My goal is to be able to rank the values column by sector.
I've rewritten the question to be more clear.
https://docs.google.com/spreadsheets/d/10YFyzc9EymEYsMkIDKNG90eFOGao2HxGOp2hlV3IdW8/edit#gid=0
My goal is to be able to rank the values column by sector.
I need permission to view your latest file.
ASKER
Sorry.
Fixed.
Fixed.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
ok, I think I misunderstood an earlier comment
One last thing, if I want to reverse the order of the ranking using SUMPRODUCT is that possible?
One last thing, if I want to reverse the order of the ranking using SUMPRODUCT is that possible?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER