Data Analysis HLOOKUP, VLOOKUP and RANK - A Doing Business in Eastern Africa 2011 Report

Wednesday, June 27, 2012

Data Analysis HLOOKUP, VLOOKUP and RANK - A Doing Business in Eastern Africa 2011 Report


Today I am specifically analyzing the Doing Business Report 2011 in Eastern Africa. I am aware only yesterday (27th June 2012) the World Bank released it's World "Doing Business Report for 2012". But I will be using the 2011 data. I will be doing a comparative analysis in my next posting.

During the 2011 period 47 countries were surveyed in the Sub Sahara Africa (SSA). In the world, 183 economies were ranked. Rankings can be read here.

Back to what I want to post about. My analysis is specifically for Eastern Africa Community (Kenya, Uganda, Rwanda, Burundi and Tanzania)  - I hear South Sudan and Somalia have applied for membership though they were not among those economies surveyed.

Of the 47 economies, I would like to extract and rank the 5 Eastern Africa states using VLOOKUP, HLOOKUP and RANK.

Step1: Naming the data: On the"Rankings Data" sheet, I gave a name called "data" to all the economies starting A1:L47

Step2: The sheet "EAC Analysis", I would like to extract the data for Kenya on cell B3 using the formula: =VLOOKUP($B$2,data,2,0)

Where B2 (with dollar signs to reference it absolutely) referring to Kenya, "data" is the named ranged for the 47 economies, 2 is the column referencing the data "Ease of Doing Business Rank" in column B (Here VLOOKUP counts from left)
When pulled down (please not you will have to edit the columns to 3,4,5,... depending on the column you want to reference.
The table will look like this:

World 2011 Doing Business Report - Eastern Africa
Metric Kenya Uganda Rwanda Tanzania Burundi
Ease of Doing Business Rank 9 12 3 14 35
Starting a Business 25 27 1 20 14
Dealing with Construction Permits 2 21 13 45 39
Getting Electricity 19 27 2 8 34
Registering Property 27 22 5 37 17
Getting Credit 2 6 2 12 39
Protecting Investors 16 23 3 16 5
Paying Taxes 36 14 3 25 23
Trading Across Borders 20 34 31 7 42
Enforcing Contracts 23 20 3 1 41
Resolving Insolvency 12 3 36 19 38


Now from the table above, I would like to extract the rankings on row 2 using HLOOKUP ("Ease of Doing Business Rank" and using the RANK formula to get the rank in Eastern Africa:
The synstax will go like this: =HLOOKUP(A17,$A$2:$F$13,2,0) - Here HLOOKUP looks for data in a row, by looking for A17 (The country) in the data A2:F13 (The table above) and giving us what is row number 2 and "0" for "Exact match"

Next we use the RANK formula to rank the economies based on the ranking displayed by the formula above (HLOOKUP) in column B17:B21.

The sytax for RANK is =RANK(number, ref,[order]). Here the number is the actual number you want to rank, ref - is the array, while Order - is either descending "0" or ascending - "1"

The formula on column C "rank" will appear like this: =RANK(B17,$B$17:$B$21,1)

Here I am using 1 as the order since I want the country with the biggest number to appear as a last performing in terms of doing business.

Conclusion: in 2011 Rwanda was ranked first in doing business report, ranking 3rd in Sub Sahara Africa. Mauritius and South Africa came first and second respectively while Kenya came a distant 9th.

-----------------------------
Download the detailed analysis here

Want more analysis email me




0 comments: