Excel: Lorenz Curve and Gini Coefficient - Full Tutorial
ฝัง
- เผยแพร่เมื่อ 22 มี.ค. 2020
- This video tutorial shows, how to draw the Lorenz Curve in Microsoft Excel and afterwards, it's shown how to calculate the Gini Coefficient.
You are most likely here, because you are a student of economics. If you want to boost your career/increase your job chances, I'll recommend you to learn RPA (software robots), which is automation of processes. With an RPA tool you can automate everything a human can do and it is really easy. The following two video tutorials take you from a beginner to being able to create your own scripts. UiPath or Power Automate, if you dont know, what to chose, I would go with UiPath:
🔵 UiPath RPA Beginners Tutorial - • UiPath RPA Beginners T...
🔵 Power Automate for Beginners - • Microsoft Power Automa...
0:12 Presenting the Lorenz/Gini Case
Our data set is 5 regions with population and income for each. If your data is in absolute numbers, you need to have them in %. We store the data in our Excel sheet.
0:45 Calculating the income to population share
We divide the percentage of income by the percentage of population. A high number means a high income per capita for that region and similarly a low number means a low income per capita for the region. Finally sort the data from the smallest to the biggest, giving us the poorest region first.
1:45 Calculating the cumulative population and income
Calculate the cumulative population and income.
2:29 Draw the Lorenz curve in Excel
Mark the data, click Scatter, click Scatter with Smooth Lines and Markers. This gives you the raw Lorenz curve.
2:41 Fine tuning our Lorenz curve
Fix the max of the axis. Have the units in 10% increments. Make a chart title. Give the Lorenz curve a name. Axis titles.
4:10 Make an equality curve
This is how the Lorenz curve looks like, if everyone had the same income (= the regions are all equal).
5:01 Calculating the Gini coefficient
The gini coefficient is a statistical measure, that represents the income inequality withing a group of people (e.g. a nation, a region etc.).
5:22 Calculating the area under the Lorenz curve
6:44 Calculating the area between the inequality curve and the Lorenz curve
Connect with me:
💼 LinkedIn - / andersjensens
👥 Facebook - / andersjensenorg
💌 Email Newsletter - andersjensen.org/email-newsle...
#excel #statistics
Great explanation, love the simplicity and clarity, thanks
Hey Diego. Thank you - nice that you took your time to comment :) Kind regards, Anders
Thank you very much for the amazing content! Really well explained.
Hey Mario. Thanks a lot 😊 I forgot about this video. Have a great week. Kind regards, Anders
Thank you so much for your wonderful tutorial. It's very informative and easy to digest. :-)
You're very welcome, ベンツ Bentsu's diary - your support helps me a lot 😍
thank you so much!! this helped me immensely!
Hey Eridingle. Thanks a lot for letting me know 😊 Kind regards, Anders
Perfect! That´ll help a lot!
Thank you, Paulo 🙂👍
Hi Andres, thanks for the great explanation. I followed your instructions but I am stuck at a point. I am trying to create the equality line but when I edit graph data, the pop-up box (at 04:45) asks only for 'series name' and 'series values' instead of separate sections for 'series x value' and 'series y values'. What can I do?
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 12,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): th-cam.com/video/xWFz-S96XGo/w-d-xo.html Kind regards, Anders
my friend thank you for this great video. I only have one question: where did you get this data from, I mean the data where you based your calculations, the starting data of population and income. Is it for a specific country, which source did you use?
Thank you, Nikos!! Great question, and no it was just random data :) Kind regards, Anders
@@andersjensenorg ok I see, thank you. I was wondering if you could help me with the following: I am doing a project for school and I have also used random data to calculate the Gino coefficient, and my teacher wants me to justify the validity of this data. Do you have any idea for how I could do that? Basically I have to show that my data is realistic and not abnormal. How could I show that my data is valid?
Thank you!
Hey Claudia, you're very welcome :) Kind regards, Anders
thx for the great video.
if possible could you provide citations or the source for the calculations?
Hey shastock. Thank you. Yeah for sure, check this: towardsdatascience.com/clearly-explained-gini-coefficient-and-lorenz-curve-fe6f5dcdc07 Kind regards, Anders
@@andersjensenorg thx for the anwser and the fastest replay ever :D
sir if i want to calculate gini coefficient of a country from 1990-2019 yearly then kindly if you could guide me about which variables data should i take.
Hey Alian. Thanks for the question. At the moment, I’m on paternity leave, meaning I don’t produce videos and solve problems until the 23rd of August. Feel free to post the problem at my Discord, where many bright automation minds are gathered: th-cam.com/channels/Pdtz4gd_iYebJFYq9N8pWA.htmlcommunity?lb=UgxD1Qe9lWKL1I7HyAJ4AaABCQ Kind regards, Anders
hi Anders, I am using raw numbers instead of % of income and population. what do i do?!
Thanks for writing 😊 I'm getting more than 50 messages daily. While I read all of them, I can’t reply to everyone 😔 But I’ve created an RPA/Automation community where we’re 11,000+ RPA Developers helping each other with solutions and our careers. Here’s the video on how to join (the invitation link is in the video description): th-cam.com/video/xWFz-S96XGo/w-d-xo.html Kind regards, Anders
Ty
YW 😊
Thanks for your video. May be can you help me.
Could you explain the differences between this two formulas for gini calculation: www.paho.org/English/SHA/be_v22n1-Gini.htm and Brown formula: www.statsdirect.com/help/nonparametric_methods/gini_coefficient.htm) / transportgeography.org/contents/methods/gini- coefficient /.
What kind of method could be better if you have a list of countries population and global income by country. Thanks in advance
Hey Maurici. I honestly don't know. I made the videos when I studied economics, because my fellow students had problems with it. Now I moved into IT (with my Eco background) and I'm not touching statistic much. Sorry. Kind regards, Anders
@@andersjensenorg Thanks a lot Anders !
мы с соней ничего не поняли если что мы устали
I dont understand? 🙂