Accounting for Number of Observations in a Regression when using Median data

[dross]

I'm evaluating a data set of Median product prices against a 100-point product rating scale.

The data I have to work with are below. The Correlation of Rating to Median Price is 0.922. Since all of the products rated so far have fallen into the rating range of 87 to 95 I want to predict the median price (dependent variable) for a broader range of ratings (independent variable). I have done so with a simple linear regression which yields an R-squared of 0.85 (ignoring the number of observations).

Question: Since I know the number of observations from which the Median data has been derived, should I take account of the number of observations in my analysis, and if so how?

DATA:

Observations	Rating	Median Price
4	95	92.5
6	94	82.5
10	93	49
29	92	46
31	91	45
27	90	40
22	89	45
6	88	23
1	87	13

<tbody>
</tbody>

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }.xl64 { vertical-align: middle; white-space: normal; }.xl65 { text-align: center; vertical-align: middle; white-space: normal; }.xl66 { text-align: center; vertical-align: middle; }</style>

<tbody>
</tbody>

<tbody>
</tbody>

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { text-align: center; }.xl64 { vertical-align: middle; white-space: normal; }.xl65 { text-align: center; vertical-align: middle; white-space: normal; }.xl66 { text-align: center; vertical-align: middle; }</style>

 

[shg]

I would think you'd need the underlying observations rather than the medians of those observations.

 

[dross]

I do have the observations within the rating range of 87-95. However, I've been asked to estimate median prices within the broader rating range of 85-100 from the available data. So, is what I have done valid and is there something more I could do with the known number of observations, or something else?

 

[shg]

You could calculate weighted correlation; see https://en.wikipedia.org/wiki/Pearson_correlation_coefficient#Weighted_correlation_coefficient

 

[shg]

	A​	B​	C​	D​	E​
1​	Observations​	Rating​	Median Price​
2​	wi​	xi​	yi​		Correl​
3​	4​	95​	92.5​		0.775544​
4​	6​	94​	82.5​
5​	10​	93​	49.0​
6​	29​	92​	46.0​
7​	31​	91​	45.0​
8​	27​	90​	40.0​
9​	22​	89​	45.0​
10​	6​	88​	23.0​
11​	1​	87​	13.0​

The formula in E3 is

=SUMPRODUCT(wi * (xi - SUMPRODUCT(wi * xi) / SUM(wi)) * (yi - SUMPRODUCT(wi * yi) / SUM(wi))) /
SQRT((SUMPRODUCT(wi * (xi - SUMPRODUCT(wi * xi) / SUM(wi)) ^ 2) * SUMPRODUCT(wi * (yi - SUMPRODUCT(wi * yi) / SUM(wi)) ^ 2)))

 

[dross]

Thanks for your replies. I'm quit slow on stat work. How does this help me estimate yi values that correspond to xi values of 85, 86, and 96-100?

 

[shg]

You'd need to do a weighted regression to get the slope and offset.

I'm no statistician, but doing extrapolation (versus interpolation) seems very sketchy.

 

[dross]

OK, I understand what you are saying. Thank you for your input.

 

[shg]

You're welcome.