How to Calculate Gini Coefficient in Excel

This guide will explain how to calculate the Gini coefficient in Excel.

The Gini coefficient is a measure of statistical dispersion that economists often use to represent the income or wealth inequality within a region.

The Gini coefficient was first used by Italian statistician Corrado Gini as a way to index the degree of inequality of a country’s wealth.

How is this derived mathematically? The coefficient is commonly defined through the Lorenz curve, a graphical representation of the distribution of wealth.

We can find the Gini coefficient as the ratio of the area that lies between the line of equality and the Lorenz curve over the total area under the line of equality.

The Gini coefficient allows economists to easily compare changes in income or wealth inequality. For example, a country may have had a Gini index of 0.49 in 2010. Ten years later, the Gini index may grow to 0.6. A higher Gini coefficient indicates more inequality among the population.

A Gini coefficient of 0 indicates perfect equality where every person in the population has the same income or net worth.

Suppose you are given a table that describes the Lorenz curve of a given country. The table describes how much of the population comprises certain fractions of the total income. 

For example, let’s say that the lowest 20% of earners make only 5% of the total income. The next 50% of earners make 15% of the income, and so on.

We can use Excel formulas to create a score for each fraction of the population. The Gini coefficient will then be computed from the sum of those scores.

Now that we know the purpose of Gini coefficients in economics, let’s learn how we can use an actual sample spreadsheet to calculate its value.

 

 

A Real Example of Calculating Gini Coefficient in Excel

The following section provides several examples of how to use this function. We will also go into detail about the formulas and tools used in these examples.

First, let’s take a look at our sample data. The data below divides the income into different fractions of the total. Each fraction of the total income is ordered according to the lowest-earning individuals.

incom distribution data

For example, the table above indicates that 5% of the total income comes from the bottom 10% of earners. Meanwhile, the top 5% of earners contribute to nearly half the total national income.

To find the Gini coefficient, we will need to compute a few more columns. First, for each fraction of the population, we will have to specify what percentage of the population is richer. 

We can compute this value using the following formula:

=100%-SUM($B$2:B2)

The formula above uses the SUM function to get the cumulative total and subtracts that amount from 100%.

Next, we’ll compute the score for each row. The score is obtained using the following formula:

SCORE = % of Income * (% of Population + (2 * % of Population Richer))

After computing the score of each row, we can subtract the total of all the scores from 1 to get the Gini coefficient.

calculate gini coefficient in Excel

 

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try calculating the Gini coefficient yourself, head over to the next section to read our step-by-step breakdown.

 

 

How to Calculate Gini Coefficient in Excel

This guide section will explain each step you need to start computing the Gini coefficient given income or wealth distribution data. You’ll learn how to use a simple Excel formula to give each row in your dataset a score.

Follow these steps to start calculating the Gini coefficient function:

  1. First, we’ll need to create a new column that indicates how much of the population is richer than the current population segment. We can determine this value by subtracting the current cumulative sum of column B from 100%.
    calculate percentage of population richer for each fraction
  2. Use the Fill Handle tool to complete the rest of the row.
    use Fill Handle tool
  3. Next, we’ll compute a partial score for each of the columns in our table. We’ll use the Excel formula =A2 * (B2 + 2*C2).
    compute partial score to be used to calculate gini coefficient in Excel
  4. Hit the Enter key to evaluate the formula.
    evaluate formula
  5. Use the Fill Handle tool to return the partial score for each row in our income dataset.
    get scores for every row
  6. We can now find the Gini coefficient. Subtract the sum of all the partial scores from 1 to get the final result.
    calculate gini coefficient in Excel by adding scores
  7. Hit the Enter key to evaluate the function. In this example, we can determine that our Gini coefficient is 0.52.
    calculate gini coefficient in Excel

These are all the steps you need to follow to calculate the Gini coefficient in Excel. 

 

 

This step-by-step guide should serve as a quick introduction to calculating the Gini coefficient in Excel.

You should now be able to convert income or wealth data into a specific score that you can use to derive the Gini coefficient.

Calculating the Gini coefficient is just one example of the many computations you can perform in Microsoft Excel. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like