Coefficient of Dispersion: Create a UDF to average a range of cells

[PSJupiter2]

I wish to create a UDF that calculates a formula used in Mass Appraisal of Real Estate

I use MS Excel 2007, and Windows 7

This is a sample what my Data looks like:

AssessedValue	SalePrice	Ratio = AV/SP
90	88	1.02
90	93	0.97
90	87	1.03
90	94	0.96
90	85	1.06
90	96	0.94
90	95	0.95
90	98	0.92
90	100	0.90
90	90	1.00
90	90	1.00
90	98	0.92
90	97	0.93
90	90	1.00

<tbody>
</tbody>

My data ranges will be different every time I use the UDF ("n" is based on the number of data points available)


To my knowledge, there is no built-in formula for the following:

Control - Shift - Enter:
{=((AVERAGE(ABS(C2:C15-(MEDIAN(C2:C15)))))/(MEDIAN(C2:C15)))*100}


I thought a UDF would be the way to go. I am trying to accomplish the following:


Function COD(Ratio, Median)

COD= {((AVERAGE(ABS("Range of available data" - "Median of Available Data"))))/(MEDIAN("Median of Available Data"))*100}

End Function


Any help on this would be greatly appreciated! Thank You!

 

[shg]

Isn't the coefficient of dispersion the variance divided by the mean?

=VARP(data)/Average(data)

 

[PSJupiter2]

Isn't the coefficient of dispersion the variance divided by the mean?

=VARP(data)/Average(data)

Hi shg:

Thanks for the quick reply. Unfortunately, VARP uses the average of the data set. In the Mass Appraisal context, Coefficient of Dispersion uses the Median to test for average deviation.

Average Deviation = [ ∑ |Ratio – Median| ] / n

COD = [ (average deviation) / Median] * 100
While your formula would work for the average, it will not work for my purposes.

Thanks again for the suggestion!

 

[shg]

Cheap & cheerful:

Function COD(r As Range) As Double
  Dim sAdr As String
  Dim sFrm As String
  
  sAdr = r.Address
  sFrm = "average(abs(" & sAdr & " - median(" & sAdr & ")))/median(" & sAdr & ")"
  COD = r.Worksheet.Evaluate(sFrm)
End Function

Row\Col	A​	B​	C​	D​
1​	61​		0.482609​	C1: {=AVERAGE(ABS(A1:A10 - MEDIAN(A1:A10)))/MEDIAN(A1:A10)}
2​	27​		0.482609​	C2: =COD(A1:A10)
3​	65​
4​	74​
5​	90​
6​	53​
7​	14​
8​	36​
9​	5​
10​	39​

 

[PSJupiter2]

Thank You shg!!

This worked just as intended. Now I just need to learn the components of this formula!

Cheap & cheerful:

Function COD(r As Range) As Double
  Dim sAdr As String
  Dim sFrm As String
  
  sAdr = r.Address
  sFrm = "average(abs(" & sAdr & " - median(" & sAdr & ")))/median(" & sAdr & ")"
  COD = r.Worksheet.Evaluate(sFrm)
End Function

Row\Col	A​	B​	C​	D​
1​	61​		0.482609​	C1: {=AVERAGE(ABS(A1:A10 - MEDIAN(A1:A10)))/MEDIAN(A1:A10)}
2​	27​		0.482609​	C2: =COD(A1:A10)
3​	65​
4​	74​
5​	90​
6​	53​
7​	14​
8​	36​
9​	5​
10​	39​

<tbody>
</tbody>

 

[shg]

You're welcome.