1
$\begingroup$

Let's say I have 5 known data points with coordinates

$X$ : Area under curve

$Y$ : Activity

The 5 points have individual error ($\Delta X_{i}$,$\Delta Y_{i}$) on both $X$ and $Y$ and I know that the best fit of these 5 points is a linear fit.

After plotting my 5 original points, I have another one that I only know the Area under curve and not the activity. With the fit equation of my 5 original points, I can associate an Activity to my 6th point. Is there a way to provide a uncertainty to my 6th point ? I have some ideas about Max/Min slopes but I don't know if it's the best way in this situation... And I have to use only Excel at the request of someone.

Thank you !!

$\endgroup$
1
  • $\begingroup$ In order for us to answer this question correctly, could you please explain how you perform your line fitting? And if you do that with default Excel tool, how do you get errors $\Delta X_i$? $\endgroup$ Dec 4, 2012 at 16:11

1 Answer 1

-1
$\begingroup$

UPD. What is written below works if you estimate a line with least-squares estimation for regression model $Y = aX + b$ (that is the default in Excel). If you use different model and/or method to minimize errors both in $X$ and $Y$ - the answer is not correct.


What is a general procedure in this case is to write your model like this:

$Y = aX + b + \epsilon$

Where $a$ and $b$ are the coefficients you obtain by training your model, and $\epsilon$ is a noise. Usually it is assumed to be zero-mean and depending on unknown parameter $\sigma$. If you build your linear approximation with standart tools, it probably assumes that the noise is Gaussian $\epsilon$ ~ $N(0, \sigma)$.

So what you can do is to evaluate estimation for parameter

$\hat{\sigma^2} = \frac{1}{N}\sum_{i=1}^N(Y_i - \hat{Y_i})$

Where $N$ - number of your points, and $\hat{Y_i} = aX_i + b$ - your prediction.

If you do that, you can get confidence intervals as quantiles of $N(0, \sigma)$. For example, 95% confidence interval is approximately $[\hat{Y}_{new} - 1.96*\sigma; \hat{Y}_{new} + 1.96*\sigma]$.

You can do that with Excel.

More on mean squared error minimisation and linear regression is online.

$\endgroup$
6
  • $\begingroup$ This answer seems to miss a crucial aspect of the question, which is the error in $X$: this makes a difference. It also gives incorrect formulas for the prediction interval that is requested: such an interval must account for the error of estimation of $a$ and $b$ as well as the error in $Y$. With just five points in the dataset, it is likely the prediction interval (in the regression setting you discuss) will be much wider than what your confidence interval formula gives, so this is not just a pedantic distinction, but of practical importance. $\endgroup$
    – whuber
    Dec 4, 2012 at 15:56
  • $\begingroup$ @whuber Yes, that's true. In this case we could use multivariate Gaussian noise, for example. But as I know Excel regression build it based on just errors for $Y$, so this would not be correct. Additionally, as for prediction we are given an exact point $X_{new}$ we could project this multivariate gaussian into one dimension $Y$. And that would give as a solution like in the answer. $\endgroup$ Dec 4, 2012 at 16:08
  • $\begingroup$ I appreciate the Excel restriction, but that just means you have to program the formulas yourself rather than using the Regression add-in. Even when $X_{new}$ is known exactly, you still must account for the estimation error in $\hat{a}$ and $\hat{b}$: please review questions on our site related to prediction intervals. $\endgroup$
    – whuber
    Dec 4, 2012 at 16:11
  • $\begingroup$ Thank you Dmitry and Whuber for you quick answers. I found this topic stats.stackexchange.com/questions/33433/… and the last equation on gung's answer seems to answer my question. But I'm not sure about the $s^{2}_{error}$. Would it be $\sqrt{\frac{1}{N}\sum _{i=1}^{N}(Y_{i}-\hat{Y_{i}})^{2}}$? $\endgroup$
    – Sicnarf
    Dec 4, 2012 at 17:00
  • $\begingroup$ That's on the right track--but note it does not handle the errors in the $X_i$. Your formula for $s_2$ is not quite right: change the denominator from $N$ to $N-p$ where $p$ is the number of linearly independent explanatory variables (including the constant term). In your case, with one explanatory variable and possibly one constant, $p=2$ if you are estimating both a slope and intercept and otherwise $p=1$. Because your $N$ (equal to $5$) is so small, these adjustments are important. $\endgroup$
    – whuber
    Dec 4, 2012 at 18:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.