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

PSJupiter2 · Mar 18, 2015

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:

[TABLE="width: 250"]
<tbody>[TR]
[TD]AssessedValue[/TD]
[TD]SalePrice[/TD]
[TD]Ratio = AV/SP[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]88[/TD]
[TD]1.02
[/TD]
[/TR]
[TR]
[TD]90
[/TD]
[TD]93[/TD]
[TD]0.97[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]87[/TD]
[TD]1.03[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]94[/TD]
[TD]0.96[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]85[/TD]
[TD]1.06[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]96[/TD]
[TD]0.94[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]95[/TD]
[TD]0.95[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]98[/TD]
[TD]0.92[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]100[/TD]
[TD]0.90[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]90[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]90[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]98[/TD]
[TD]0.92[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]97[/TD]
[TD]0.93[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]90[/TD]
[TD]1.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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 · Mar 18, 2015

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

=VARP(data)/Average(data)

PSJupiter2 · Mar 19, 2015

shg said:
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 · Mar 19, 2015

Cheap & cheerful:

Code:

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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][/tr]
[tr][td]

1

[/td][td]

61

[/td][td][/td][td]

0.482609

[/td][td]C1: {=AVERAGE(ABS(A1:A10 - MEDIAN(A1:A10)))/MEDIAN(A1:A10)}[/td][/tr]

[tr][td]

2

[/td][td]

27

[/td][td][/td][td]

0.482609

[/td][td]C2: =COD(A1:A10)[/td][/tr]

[tr][td]

3

[/td][td]

65