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

How to generate One Million Random Phone Numbers using Excel

Suppose you want to send a random SMS to more than One million numbers and you wanted to use Excel to generate random unique numbers (Pardon me if bulk SMS is done under license in your territory)

(Download the WorkBook with 1,000,000 Numbers here )
More often marketers are confronted with the cost of buying phone number databases when charting new markets or introducing new products. This post will help you generate random numbers (unverified).

Step 1: Identify your preferred prefix (Telco) and the Country Code (for example +254-722 for Safaricom Kenya).

Step 2: Use the RandBetween Formula in Excel to randomize the remaining 6 characters including the changing last number (i.e 07YY XXX XXX) - The changing prefix character is YY, while the phone number that is randomized between 0 and 9 is the XXX XXX is the Suffix (Individual Phone Number)

Step 3: Enter the Country Code, the Prefix and the Suffix in individual cells (as seen in here)

Step 4: Enter the RandomBetween Formula in the cells in each suffix with the following syntax =RANDBETWEEN(0,9). This essentially generates random numbers between 0 and 9 in the target cells (You can use F9 to r-generate the numbers). Use the =CONCATENATE to combine the numbers (country code, suffix and prefix - to get the number in Col. O.


Step 5: Pull down the numbers to the extent possible that you may require ( I have 20,000 plus numbers in my excel sheet that can be downloaded here)

Step 6: Now check for duplicates by going to Data > Remove Duplicates

Step 7: Now you can send out as many SMS as possible - may be you will need me for a bulk SMS tool!

------
Please check with your regulatory authority for bulk SMS licenses and how it is governed.

Using Excel to calculate Two-Variable Correlation

• Correlation coefficient.
• Calculation using the Data Analysis Add-in.
• Calculation using the CORREL function.
  
• Covariances.

CORRELATION COEFFICIENT
The correlation coefficient between two series, say x and y, equals
  Covariance(x,y) / [Sqrt(Variance(x)) * Sqrt(Variance(y))]
where

• Covariance(x,y) is the sample covariance between x and y:  (1/(n-1)) × Σ _i (x_i - xbar)(y_i - ybar)
• Variance(x) is the sample variance of x: (1/(n-1)) × Σ _i (x_i - xbar)²
• Variance(x) is the sample variance of y: (1/(n-1)) × Σ _i (y_i - ybar)² 

CALCULATION USING THE DATA ANALYSIS ADD-IN (Lean how to activate it here)

The data used can be downloaded here here
We consider only two series, but we could do the same for more than two series.

• In the Data Group select the Data Analysis Add-in
• Select Correlation
• Fill out the Correlation dialog box as below

The Data, Click on Data Analysis (Data > Data Analysis) 

When you click data analysis,

When you click OK,

  The Result

 

ADDING-IN THE DATA ANALYSIS TOOLPACK TO EXCEL

ADDING-IN THE  DATA ANALYSIS TOOLPACK TO EXCEL
Statistical analysis such as descriptive statistics and regression requires the Excel Data Analysis add-in.
The default configuration of Excel does not automatically support descriptive statistics and regression analysis.
You may need to add these to your computer (a once-only operation).

Excel 2007: The Data Analysis add-in should appear at right-end of Data menu as Data Analysis.
If not then

1. Click the Microsoft Office Button , and then click Excel Options.
2. Click Add-Ins, and then in the Manage box, select Excel Add-ins.
3. Click Go.
4. In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK.
   Tip  If Analysis ToolPak is not listed in the Add-Ins available box, click Browse to locate it.
   If you get prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.
5. After you load the Analysis ToolPak, the Data Analysis command is available in the Analysis group on the Data tab.
6. If you have trouble see: http://office.microsoft.com/en-us/excel/HP100215691033.aspx:

Excel 2003: The Data Analysis add-in should appear in the Toools menu.
If not then

1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available box, select the check box next to Analysis Toolpak, and then click OK.
   Tip   If Analysis Toolpak is not listed, click Browse to locate it.
3. If you see a message that tells you the Analysis Toolpak is not currently installed on your computer, click Yes to install it.
4. Click Tools on the menu bar. When you load the Analysis Toolpak, the Data Analysis command is added to the Tools menu.
5. If you have trouble see http://office.microsoft.com/en-us/excel/HP011277241033.aspx