Hi,

Here is my scenario. I'm trying to get the RATING of Managers of a MY CITY based on their SCORES.

MANAGERS SCORE RATING
MANAGER 1 48.11%
MANAGER 2 47.87%
MANAGER 3 46.93%
MANAGER 4 44.97%
MANAGER 5 44.41%
MANAGER 6 44.39%
MANAGER 7 44.02%
MANAGER 8 43.82%
MANAGER 9 42.55%

But in order to get their respective ratings there are multiple Conditions that needs to be satisfied.

All conditions should be met

Rated 1
condition 1 Better than Rank #1 CITY
condition 2 Better than City Goal as long as Rank #1 City is better than City Goal.
condition 3 Top 10% of Manager Ranking
condition 4 If Rank #1 City is worse than City Goal then Goal will default to Rank #1 City.
Rated 2
condition 1 Next 25% of Manager Ranking after top 10%
condition 2 Better than City Goal as long as Rank #1 City is better than City Goal
condition 3 Better than Rank #2 City if Rank #1 City is worse than City Goal
condition 4 If Rank #1 City is worse than City Goal then Goal will default to Rank #1 City.
Rated 3
condition 1 Next 55% of Manager Stack Ranking after top 35% (combination of Rated #1 and Rated #2)
condition 2 Better than City Goal as long as Rank #1 City is better than City Goal.
condition 3 Better than My City Score if Rank #1 City is worse than City Goal
condition 4 If Rank #1 City is worse than City Goal then Goal will default to Rank #1 City.
Rated 4
condition 1 Worse than My City Score
condition 2 Below City Goal if Rank #1 City is better than City Goal
condition 3 Bottom 10% of Manager Ranking
condition 4 If Rank #1 City is worse than City Goal then Goal will default to Rank #1 City.
Rated 5
condition 1 Worse than My City Score
condition 2 Below City Goal if Rank #1 City is better than City Goal
condition 3 Bottom 3% of Manager Ranking
condition 4 If Rank #1 City is worse than City Goal then Goal will default to Rank #1 City.


Given these conditions here are the variables that are needed:

RANK CITY SCORE
1 CITY 1 75.00%
2 CITY 2 49.10%
3 CITY 3 47.90%
4 MY CITY 46.10%
5 CITY 5 45.70%
6 CITY 6 44.50%
7 CITY 7 42.90%
8 CITY 8 32.80%

CITY GOAL = 47.30%
MY CITY = 46.10%
RANK#1 CITY = 75.00%

I've been trying to use IF(AND( Function to deal with multiple conditions however there are conditions that i am unable to satisfy.

Anyones input is highly appreciated. Thanks!