Gini coefficient
 

All

I want to calculate the Gini coefficient of a series with the following
macro. Excel reports a type mismatch for sum2 which I don't understand.


Dirk

Sub gini()

Dim ran As Range
Dim sum1, sum2, sum3, sum4 As Variant
Dim i, num As Integer


'ask for the range of the variable
Set ran = Application.InputBox("type in the range of the variable",
Type:=8)
num = ran.Rows.Count

'copy and paste into column to the right, sort
Selection.Copy
ran.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=ran.Offset(0, 1), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'cumulative income
'=SUM($B$2:B2)/SUM($B$2:$B$49)
sum1 = 0
For i = 0 To num - 1
sum1 = sum1 + ran.Cells(i, 1)
Next i
sum2 = 0
For i = 0 To num - 1
sum2 = sum2 + ran.Offset(i, 1)
ran.Offset(i, 2) = sum2 / sum1
Next i

'cumulative population
'=ROWS($C$2:C2)/ROWS($C$2:$C$49)
sum3 = 0
For i = 0 To num - 1
sum3 = sum3 + ran.Offset(i, 1)
ran.Offset(i, 3) = sum2 / num
Next i

'the product in the gini equation
'=(D3-D2)*(C3+C2)
sum4 = 0
For i = 1 To num
ran.Offset(i, 4) = (ran.Offset(i, 3) - ran.Offset(i - 1, 3)) *
(ran.Offset(i, 2) _
+ ran.Offset(i - 1, 2))
sum4 = sum4 + ran.Offset(i, 4)
Next i

'calculate coefficient
With ran.Offset(-1, 4)
..Value = "Gini"
..Font.Bold = True
End With
With ran.Offset(0, 4)
..Value = Abs(1 - sum4)
..Font.Color = 1
End With

End Sub

Gini coefficient
 

Hi Dirk,

Take a look at
http://www.wessa.net/co.wasp

HTH
Carim

Gini coefficient
 

Your problem with the line

sum2 = sum2 + ran.Offset(i, 1)
ran.Offset(i, 2) = sum2 / sum1

is that ran is a multi-cell range, so the offset is also a multi-cell range.

Instead, you should use (perhaps! not sure of your logic)

sum2 = sum2 + ran(1,1).Offset(i, 1).Value
ran(1,1).Offset(i, 2) = sum2 / sum1

Note that you will need to change your code blocks for sum3 and sum4 as well.

HTH,
Bernie
MS Excel MVP


"Dirk" wrote in message
ups.com...
All

I want to calculate the Gini coefficient of a series with the following
macro. Excel reports a type mismatch for sum2 which I don't understand.


Dirk

Sub gini()

Dim ran As Range
Dim sum1, sum2, sum3, sum4 As Variant
Dim i, num As Integer


'ask for the range of the variable
Set ran = Application.InputBox("type in the range of the variable",
Type:=8)
num = ran.Rows.Count

'copy and paste into column to the right, sort
Selection.Copy
ran.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Sort Key1:=ran.Offset(0, 1), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'cumulative income
'=SUM($B$2:B2)/SUM($B$2:$B$49)
sum1 = 0
For i = 0 To num - 1
sum1 = sum1 + ran.Cells(i, 1)
Next i
sum2 = 0
For i = 0 To num - 1
sum2 = sum2 + ran.Offset(i, 1)
ran.Offset(i, 2) = sum2 / sum1
Next i

'cumulative population
'=ROWS($C$2:C2)/ROWS($C$2:$C$49)
sum3 = 0
For i = 0 To num - 1
sum3 = sum3 + ran.Offset(i, 1)
ran.Offset(i, 3) = sum2 / num
Next i

'the product in the gini equation
'=(D3-D2)*(C3+C2)
sum4 = 0
For i = 1 To num
ran.Offset(i, 4) = (ran.Offset(i, 3) - ran.Offset(i - 1, 3)) *
(ran.Offset(i, 2) _
+ ran.Offset(i - 1, 2))
sum4 = sum4 + ran.Offset(i, 4)
Next i

'calculate coefficient
With ran.Offset(-1, 4)
.Value = "Gini"
.Font.Bold = True
End With
With ran.Offset(0, 4)
.Value = Abs(1 - sum4)
.Font.Color = 1
End With

End Sub