PSJupiter2
New Member
- Joined
- Mar 18, 2015
- Messages
- 7
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:
<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
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:
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<tbody> </tbody> |
<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 |
<tbody> </tbody> | 315000 | .76 | 413270 | 414885 | -0.01 | 18.66 | 6 | ||||||||
4 |
<tbody> </tbody> | 319300 | .75 | 418911 | 421856 | -0.01 | 18.69 | 7 | ||||||||
5 |
<tbody> </tbody> | 326300 | .77 | 428095 | 426447 | 0.01 | 18.70 | 5 | ||||||||
6 |
<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
Last edited: