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