Slope Array Formula with Conditions #Num! Error

[PSJupiter2]

Hello,

I am trying to use one array formula to recreate the following linear regression formula:

Y = (SalesRatio-MedianSalesRatio)/MedianSalesRatio
X = LN(((EMV/MedianSalesRatio)⁄(2))+ (SalePrice⁄(2)))/LN(2)

Here is my data:

[TABLE="class: outer_border, width: 350, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 103"]
<tbody>[TR="class: outer_border"]
[TD="align: center"]SalePrice[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR="class: outer_border"]
[TD="align: center"]Estimated Market Value (EMV)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR="class: outer_border"]
[TD="align: center"]SalesRatio
(EMV / SalePrice)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR="class: outer_border"]
[TD="align: center"]AdjustedValue
EMV / MedianSalesRatio[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR="class: outer_border"]
[TD="align: center"]Proxy Value
(AdjustedValue / 2) + (SalePrice / 2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR="class: outer_border"]
[TD="align: center"]Y = SalesRatio-MedianSalesRatio / MedianSalesRatio[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 136"]
<tbody>[TR="class: outer_border"]
[TD="align: center"]X = LN(ProxyValue) / LN (2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR="class: outer_border"]
[TD="align: center"]Condition[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]D[/TD]
[TD="align: right"]E[/TD]
[TD="align: right"]F[/TD]
[TD="align: right"]G[/TD]
[TD="align: right"]H[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]206500[/TD]
[TD="align: right"]198500[/TD]
[TD="align: right"].96[/TD]
[TD="align: right"]260425[/TD]
[TD="align: right"]233463[/TD]
[TD="align: right"].26[/TD]
[TD="align: right"]17.83[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]225900[/TD]
[TD="align: right"]208200[/TD]
[TD="align: right"].92[/TD]
[TD="align: right"]273152[/TD]
[TD="align: right"]249526[/TD]
[TD="align: right"].21[/TD]
[TD="align: right"]17.93[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR="class: outer_border"]
[TD="width: 103, align: right"]416500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]315000[/TD]
[TD="align: right"].76[/TD]
[TD="align: right"]413270[/TD]
[TD="align: right"]414885[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"]18.66[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR="class: outer_border"]
[TD="width: 103, align: right"]424800[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]319300[/TD]
[TD="align: right"].75[/TD]
[TD="align: right"]418911[/TD]
[TD="align: right"]421856[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"]18.69[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR="class: outer_border"]
[TD="width: 103, align: right"]455300[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]326300[/TD]
[TD="align: right"].77[/TD]
[TD="align: right"]428095[/TD]
[TD="align: right"]426447[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]18.70[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR="class: outer_border"]
[TD="width: 103, align: right"]576600[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"]379900[/TD]
[TD="align: right"].66[/TD]
[TD="align: right"]498416[/TD]
[TD="align: right"]537508[/TD]
[TD="align: right"]-0.14[/TD]
[TD="align: right"]19.04[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD="align: right"]MEDIAN SALES RATIO[/TD]
[TD="align: right"]0.76[/TD]
[TD][/TD]
[TD][/TD]
[TD]Condition[/TD]
[TD]Price Related Bias[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]?????[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Price Rel Bias[/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]?????[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD][/TD]
[TD]Manually Calc[/TD]
[TD="align: right"]

-31.17%​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]?????[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD]Formatically[/TD]
[TD="align: right"]-31.17%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



The goal is to calculate what is called a "Price Related Bias" for each "Condition". My actual table will have thousands of rows.

I want the calculation to be in one array formula for each condition

The formula for the entire sample (no conditions) is:

[ SLOPE(((C2:C6-$C$7)/$C$7),LN((((B2:B6)/$C$7)*0.5)+((A2:A7)*0.5))/LN(2)) ]

I believe the formula for finding this Price Related Bias for "Condition 5" should be:

[ { SLOPE(((IF(H2:H7=G8,C2:C7)-MEDIAN(IF(H2:H7=G8,C2:C7))/MEDIAN(IF(H2:H7=G8,C2:C7)))),(LN(((IF(H2:H7=G8,B2:B7)/MEDIAN(IF(H2:H7=G8,C2:C7)))*0.5)+((IF(H2:H7=G8,A2:A7))*0.5)))/(LN(2))) } ]

With a formula result of:

-0.2754

However the formula returns the #NUM! error

I am stumped. Both sides of the regression formula work on their own (using an array entry). I am not sure if this problem lies in my formula or if my formula is too simple.

Any help would be greatly appreciated.

MS Excel 2007
Windows 7

Best Regards,

PSJupiter2