Need Help on Country Ranking/Scoring Analysis

[Brinish Vyas]

Hi,

I need assistance in developing a country ranking/scoring system. Here is the case...

For example, I have 10 countries and there are 3 parameters (incidence rate, number of patients and number of doctors available) on basis of which I want to derive country recommendation status (strongly recommended, recommended and not recommended).

The condition is:
Higher the incidence, number of patients and doctors - stronger the recommendation.

Sample file attached for reference.

I believe this can be done by ranking these three parameters or assigning a score based on the value ranking within the dataset.

Can someone please assist how to determine these recommendation status.

Thank you in advance!

 

[Hui]

It is typical to have a cummulative mechanism so that the three scores can be added together generally as a percentage
so that for example:
Incidence Rate would maybe be 50%
No of Patients 30%
Number of Doctors 20%
Total 100%

Then for each criteria I would award the maximum % for the highest and then prorata the rest
eg: Incidence rate Hong Kong 11.4 would be 50%
Australia at 9.9 would be (9.9/11.4)*50% etc

Then add up all the ratings


See attached file:

 

[Brinish Vyas]

Dear Hui,

Thank you for your reply. I really appreciate it. Just one additional question. Will this recommendation be still valid in case some of the values are not available in any of the column. See the snapshot.

Also, how to assign percentage rating when the condition is:
lower the value - higher the score or rank

 

[Hui]

That country will get a zero rank for that category
Is that what you want ?

If so it's quite valid

Remember that the 50%, 30% and 20% ratings were just numbers that I made up
You should determine how you want the scores distributed

 

[Brinish Vyas]

Hi Hui,

Well, country getting zero rank for any of the parameter is fine. I just wanted to check if this is fine for cumulative recommendation percentage.

Secondly, I have added two more parameters and have assigned the percentage weightage for all the parameters as I wanted. Now I understand that the highest value in the particular parameter will get the percentage score of the cap that we have assigned and other values will be ranked on prorata basis.

I have one parameter named "No. of Ongoing Research". Now for this, lower the number of ongoing research, higher the ranking/scoring should be.

So, the country with lowest number of ongoing research should get (highest) percentage score of the cap that we have assigned. i.e. 20%. Rest of the countries should get on prorata basis. The key here is, the scoring/ranking needs to be done inversely because lower the competition with ongoing research, country recommendation increases.

Does this help?

Best Regards,
BV

 

[Hui]

Maybe something like E19: =E$16*IFERROR(MIN(E$5:E$14)/E5,0)
Which gives the inverse

 

[Brinish Vyas]

Perfect...Thank you so much Hui. This satisfy my need. Thanks again!!