ExcelBanter - View Single Post - Creating a Custom Excel Function to Calculate Gini Coefficients

wrote...
....
I have noticed one thing though. In order for the calculation to be
accurate, there cannot be any missing data in the array. At first I
thought that maybe the functions are treating the missing values as
zeros, but they are not. The effect of the missing values on the gini
coefficient is less than zero. Is there something that can be added to
function to have it ignore missing values?

Your original formula, paraphrased as

=SUM(ABS(x-TRANSPOSE(x)))/(2*AVERAGE(x)*COUNT(x)^2)

would also have given overstated results if there were any blank values
in x because any & all blank values in the x-TRANSPOSE(x) term would be
replaced with numeric zeros. For example, if x were only {1;<blank;1},
your original formula would return 0.5 rather than 0. Given this, I had
assumed you'd never have blank values.

However, if some values in x may not be numeric, then use the array
formula

=AVERAGE(IF(ISNUMBER(x)*ISNUMBER(TRANSPOSE(x)),
ABS(x-TRANSPOSE(x))))/AVERAGE(x)/2

or modify the gini udf as follows.

Function gini(ParamArray a() As Variant) As Double
Dim n As Double, d As Double, c As Double
Dim v As Variant, i As Long, j As Long, k As Long

v = ravel(a)
k = UBound(v)

For i = 1 To k
If Not IsEmpty(v(i)) And IsNumeric(v(i)) And VarType(v(i)) <
vbString Then
d = d + v(i)
c = c + 1
For j = i + 1 To k
If Not IsEmpty(v(j)) And IsNumeric(v(j)) And VarType(v(j)) <
vbString Then
n = n + Abs(v(i) - v(j))
End If
Next j
End If
Next i

gini = n / d / c
End Function