Japanese

How to get Gini's coefficient using Excel

Gini's coefficient is easily calculated with the use of Excel Application. This coefficient measures the degree of inequality in income of community. This value ranges between 0 to 1, where extreme zero value means an ideal equlity.

Gini's coefficient is defined as , where ‚™‚P, ‚™2, cc ‚™n are distribution of individual income of n samples,
and f is an average value of them.
Therefore f is given as f = ( ‚™‚P + ‚™2 + cc + ‚™n) / n

The values ‚™‚P, ‚™2, c‚™n are filled in painted cells as demonstrated in the figure above.

C3 cell is typed as w =abs(c$2-$b3) x , and all cells in the table are copied entirely.

(note) Excel's ABS-function gives Absolute value, i.e. | 15 - 40 | equals to 25.

B8 cell is typed asw =sum(c3:g7) x , indicating the value in the Gini's definition.

B9 is an average value of n samples i.e. w =average(b3:b7) x and B10 consists of w =count(b3:b7) x, meaning the count of values corresponding the number of sample (n).

Gini's coefficient, B11 cell is calculated as w =b8/(2*b10*b10*b9) x.

Description of B11 is also available as w =b8/(2*b10^2*b9) x. w ^2 x covers a value of number square, and w ^3 x covers that of cubic.


You can get a similar result if you copy the subsequent table and paste on your Excel Sheet. The item "sample" must be set in A1 cell.

   
sample
15254080180
15=ABS(C$2-$B3)=ABS(D$2-$B3)=ABS(E$2-$B3) =ABS(F$2-$B3)=ABS(G$2-$B3)
25=ABS(C$2-$B4)=ABS(D$2-$B4)=ABS(E$2-$B4) =ABS(F$2-$B4)=ABS(G$2-$B4)
40=ABS(C$2-$B5)=ABS(D$2-$B5)=ABS(E$2-$B5) =ABS(F$2-$B5)=ABS(G$2-$B5)
80 =ABS(C$2-$B6)=ABS(D$2-$B6)=ABS(E$2-$B6)=ABS(F$2-$B6) =ABS(G$2-$B6)
180=ABS(C$2-$B7)=ABS(D$2-$B7)=ABS(E$2-$B7) =ABS(F$2-$B7)=ABS(G$2-$B7)
total amount =SUM(C3:G7)
average (f)=AVERAGE(B3:B7)
samples (n) =COUNT(B3:B7)
Gini's coeff.i‚‡j =B8/(2*B10*B10*B9)

‚±‚̃TƒCƒg‚ňµ‚Á‚½€–Ú