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:

	SalePrice <tbody> </tbody>	Estimated Market Value (EMV) <tbody> </tbody>	SalesRatio (EMV / SalePrice) <tbody> </tbody>	AdjustedValue EMV / MedianSalesRatio <tbody> </tbody>	Proxy Value (AdjustedValue / 2) + (SalePrice / 2) <tbody> </tbody>	Y = SalesRatio-MedianSalesRatio / MedianSalesRatio <tbody> </tbody>	X = LN(ProxyValue) / LN (2) <tbody> </tbody>	Condition <tbody> </tbody>
	A	B	C	D	E	F	G	H
1	206500	198500	.96	260425	233463	.26	17.83	5
2	225900	208200	.92	273152	249526	.21	17.93	5
3	416500 <tbody> </tbody>	315000	.76	413270	414885	-0.01	18.66	6
4	424800 <tbody> </tbody>	319300	.75	418911	421856	-0.01	18.69	7
5	455300 <tbody> </tbody>	326300	.77	428095	426447	0.01	18.70	5
6	576600 <tbody> </tbody>	379900	.66	498416	537508	-0.14	19.04	6
7		MEDIAN SALES RATIO	0.76			Condition	Price Related Bias
8						5	?????
9			Price Rel Bias			6	?????
10		Manually Calc	-31.17%​			7	?????
11		Formatically	-31.17%

<tbody>
</tbody>

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