Menu

Creating a Custom Excel Function to Calculate Gini Coefficients

Search this Thread

Prev

Previous Post

Next Post

Next

#4

Report Post

Posted to microsoft.public.excel.worksheet.functions

Harlan Grove

Posts: n/a

Default

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

Reply With Quote

Reply With Quote

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Posting Rules
Smilies are On [IMG] code is On HTML code is Off Trackbacks are On Pingbacks are On Refbacks are On

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
IS THERE AN INTERPOLATION FUNCTION IN EXCEL 2003	Cooper	Excel Worksheet Functions	2	December 23rd 05 04:51 AM
Excel 2003 Slow Function Argument Window	[email protected]	Excel Discussion (Misc queries)	2	June 28th 05 06:53 PM
Excel option to store trendline's coefficients in cells for use	Miguel Saldana	Charts and Charting in Excel	9	June 20th 05 08:45 PM
Creating custom colours in Excel 2002	Fletch	Excel Discussion (Misc queries)	2	March 1st 05 05:55 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE.	Robert AS	Excel Worksheet Functions	4	December 2nd 04 10:49 AM

All times are GMT +1. The time now is 08:47 AM.

About Us

"It's about Microsoft Excel"

Contact Us ExcelBanter forum home Privacy Statement