1
$\begingroup$

I used Excel to calculate the confidence interval on a predicted value, at 95% confidence interval, so to calculate t-value I used function TINV(5%,6) thats a 2.5% and 2.5% split on each side, where 6 is degree of freedom.

But in R, the predict function, when I give level= 0.95, I get a different interval range, however giving level=0.975 gives me the same answer as excel.

So, seems like in predict when you give level=0.975, then it takes a split of 2.5% and 2.5% on each side

But all these websites, For example, another example, say we are looking for 95% confidence interval giving the formula level=0.95 (this would mean a 5% split on each side), but thats 90%, the level should be 0.975 for a 95% interval.

Whats going on? I am probabily getting confused.


EDIT:

predict(model, data.frame(c= 12.75, p= 6, f=8), level = 0.975, interval = "confidence")

The model here is a multiple linear regression

Data:

y <- c(85.10,106.30,50.20,130.60,54.80,30.30,79.40,91.00,135.40,89.30) # Total Sales

c <- c(8.50,12.90,5.20,10.70,3.10,3.50,9.20,9.00,15.10,10.20) # production cost

p <- c(5.10,5.80,2.10,8.40,2.90,1.20,3.70,7.60,7.70,4.50) # Promotion cost

f <- c(4.70,8.80,15.10,12.20,10.60,3.50,9.70,5.90,20.80,7.90) #First year box office

model <- lm(y ~ c + p + f)

Excel:

I have marked in yellow the forcasting in Excel

The problem is, with Excel I get forecast of 106.72 with upper 119.35 and lower 93.36 with =tinv(5%,6)

With R I get forecast of 106.72 with upper at 117.7 and lower at 95.65, level=0.95

With level=0.975 I get exact values as Excel.


In Excel:

=tinv(5%,6) = 2.45``Variance = 5.46

106.72 +/- tvalue*variance : 119.35 93.36

In R:

se.ci <- predi$se.fit # Value: 4.518

alpha <- qt((1-0.95)/2,6) # Value: -2.45

predi$fit[1] + c(alpha, -alpha) * se.ci # gives me 117.77165 95.65941

As you can see the tstat value is the same but prediction is different.

But when I do this:

alpha <- qt((1-0.975)/2,6) # Value: -2.968

I get 93.30182 120.12924 same as excel! (Using level=0.975 in predict gets me the answer, hence the confusion)

$\endgroup$
7
  • 3
    $\begingroup$ This appears to be a question just about how these R & Excel functions work. As such, it is off topic here. If you have a statistical question about confidence intervals, please edit to clarify. FWIW, I think the problem is with the Excel function; it should be =tinv(.025, 6). $\endgroup$ May 7, 2017 at 1:39
  • $\begingroup$ Oh sorry about that, do I post this again to stackoverflow or can this be moved directly? $\endgroup$
    – SamFlynn
    May 7, 2017 at 2:08
  • 1
    $\begingroup$ To be on topic on Stack Overflow, it will need a reproducible example. Can you provide one? $\endgroup$ May 7, 2017 at 3:47
  • 1
    $\begingroup$ There is a statistical question here: because there is a discrepancy between how you perform a manual calculation (using Excel) and how it is done automatically in R, then either you are misunderstanding the calculation or making an error. Unfortunately you haven't displayed your manual calculation explicitly, so we have little to go on. $\endgroup$
    – whuber
    May 7, 2017 at 16:51
  • $\begingroup$ @whuber Sorry, about that I have added more context. $\endgroup$
    – SamFlynn
    May 7, 2017 at 16:54

0