Ranking Help - Rank formula to exclude data from ranking (like a rank if kind of function)

Hi all,

Need some experts on this one.


Basically I have a column which contains original data.

I want to do more than just rank them. I want to rank based on a criteria. So basically want to exclude values so that it is not picked up in the rank.

is this possible. feels like a mix between a rank and an if statement.

easier to explain in the file and explanation below.



I have attached a sample file.

Book1.xlsx



The sheet explains what I'm trying to achieve. But heres some explanation to go with it.

Column B - I have created a sample data set. Basically listing a number of cats from 1 to 100.

Column D - A simple rank (high to low). So ranking the data in column B but from High to low. So the number 1 rank in this column goes to the number 100 cat in Column B.

You will see I'm using row 3 to explain the formula I have used for future reference.

Now for column E, F and G I' have given three scenarios for ranking.

Column E - Rank the data ignoring a certain set of values below a value in column B

Column F - Rank the data ignoring a certain set of values above a value in column B

Column G - Rank the data only focusing on a particular range of data from Column B


All of these I want to rank high to low

Then the reverse you will see of the same in columns I, J, K and L


Hope that makes sense!

Help!

Thanks

Hi

Fair point. Ill have to it tomorrow morning as i have the file at work.

Basically it was to try and rank data from a column. But to ignore ranking data if it was below a particular number or even to not rank data if it was above a particular number. So for those cases the rank wld appear blank. And only data within a criteria was ranked.

Ill try and upload actual data tomorrow

Was trying to understand the concept.


Thanks for the reply

Originally Posted by Richard Buttrey

Hi,

Two points

These things are always easier to deal with if we know the results you want to see. Manually add the results and then reupload the workbook.

Clearly this is a trivial example. We often find trivial examples are not truly representative of the real data and any solution we create may not work for the real data because of various differences. Please upload a workbook with real data.

One of my friends had a go and got so far

See attached

His results are where im tryin to get to

But as his ranks were showing from 100 downwards he did a addition or subtraction at the end of the formila

Which wouldnt work on a more complex set of data

Originally Posted by harp1983

Hi

Fair point. Ill have to it tomorrow morning as i have the file at work.

Basically it was to try and rank data from a column. But to ignore ranking data if it was below a particular number or even to not rank data if it was above a particular number. So for those cases the rank wld appear blank. And only data within a criteria was ranked.

Ill try and upload actual data tomorrow

Was trying to understand the concept.


Thanks for the reply