Ranking using different combinations

[Abhi Agarwal]

Hello!

I need help with the problem below

I have two columns with Final score data in Column B and Sales Volume data in Column C

I need to rank based on the following criteria

If Final score > 4.5 then rank is "Platinum"

If Final score > 3.5 and <= 4.5 then rank is "Gold"
If Final score > 3.0 and <= 3.5 then rank is "Silver"
If Final score > 2.5 and <= 3.0 then rank is "Bronze"
If Final score <2.5 then rank is "No Tier"
And if Final score >4.5 but Sales Volume <500 then rank is "Silver"

Have attached the file with sample data

Please help

Many thanks

Abhi

 

[vletm]

Abhi Agarwal
Something like this ...

 

[Hui]

Abhi

Firstly, Welcome to the Chandoo.org Forums

I would rearrange your data slightly as shown below


Then in D10: =IF(C10>=500,VLOOKUP(B10,$E$2:$F$6,2),IF(AND(B10>4.5,C10<500),$F$7,"something else"))
Copy D10 down

 

[bosco_yip]

Another layout arrangement

D10, formula copy down :

=IF((B10>4.5)*(C10<500),F$7,LOOKUP(B10,D$2:F$6))

Regards

 

[Abhi Agarwal]

Abhi Agarwal
Something like this ...

Thanks ...Works like a charm

 

[Abhi Agarwal]

Abhi

Firstly, Welcome to the Chandoo.org Forums

I would rearrange your data slightly as shown below
View attachment 41279

Then in D10: =IF(C10>=500,VLOOKUP(B10,$E$2:$F$6,2),IF(AND(B10>4.5,C10<500),$F$7,"something else"))
Copy D10 down

Thanks Hui...

 

[Abhi Agarwal]

View attachment 41282

Another layout arrangement

D10, formula copy down :

=IF((B10>4.5)*(C10<500),F$7,LOOKUP(B10,D$2:F$6))

Regards

Thanks....Looks better organized this way

 

[Abhi Agarwal]

I have two more conditions underlined added to the existing ones.
If Final score > 4.5 then rank is "Platinum"

If Final score > 3.5 and <= 4.5 then rank is "Gold"
If Final score > 3.0 and <= 3.5 then rank is "Silver"
If Final score > 2.5 and <= 3.0 then rank is "Bronze"
If Final score <2.5 then rank is "No Tier"
And if Final score >4.5 but Sales Volume <500 then rank is "Silver"
And if Final Score >4.5 but Sales volume <2000 then rank is "Gold"
And if Sales Volume >3500 then rank is "Platinum" irrespective of Final Score

Have attached the file with sample data
Please help

Many Thanks
Abhi

 

[vletm]

Abhi Agarwal
... something like this?

 

[Abhi Agarwal]

Abhi Agarwal
... something like this?

Thanks vletm...But if you see the file which i uploaded yesterday I want to rank anyone a "Platinum" if the Sales Volume > 3500 even if the final score is less than 4.5....Can you help with that? Many thanks, Abhi

 

[bosco_yip]

I have two more conditions underlined added to the existing ones.
If Final score > 4.5 then rank is "Platinum"
If Final score > 3.5 and <= 4.5 then rank is "Gold"
If Final score > 3.0 and <= 3.5 then rank is "Silver"
If Final score > 2.5 and <= 3.0 then rank is "Bronze"
If Final score <2.5 then rank is "No Tier"
And if Final score >4.5 but Sales Volume <500 then rank is "Silver"
And if Final Score >4.5 but Sales volume <2000 then rank is "Gold"
And if Sales Volume >3500 then rank is "Platinum" irrespective of Final Score

See revised file

Regards
Bosco

 

[Abhi Agarwal]

See revised file

Regards
Bosco

Thanks a lot Bosco...It works..Many thanks

 

[vletm]

Abhi Agarwal
You asked,
Here ...

 

[vletm]

Abhi Agarwal ... hmm ...
I still checked Your case and ...

1) If Final Score is 2.5 and Sales Volume <= 3500 then
What would be Rank with Your conditions?
If Final score > 2.5 and <= 3.0 then rank is "Bronze"
If Final score <2.5 then rank is "No Tier"

2) Would if be easier for You to use this version?
... and You could easier to modify it as You need.
(even those rows from 1 to 8 could delete!)