Using Excel's regression tools to determine the 95% confidence interval
January 26, 2014 12:48 PM Subscribe
MS Excel's regression tools provide 95% lower/upper confidence results but how does one properly interpret and then express those as a single ± (plus/minus) figure?
posted by kartguy to Science & Nature (4 answers total) 2 users marked this as a favorite
The in-house literature I am now required to read at work includes frequent references to linear trends, normally expressed as a trend per decade with a '±' figure identified as the confidence interval at 95%. The underlying datapoints used to calculate the liner trend are monthly measurements (1 per month).
I now have been requested to write a memo summarizing the "statistics" of the latest dataset of 120 months. At our office, the statistics part is accomplished using MS Excel. I have taught myself how to use the Data Analysis Tools in Excel to calculate the linear trend and other regression statistics.
So, I now have the Excel stats for the newest dataset but I am not sure how to properly express them in a way that avoids my looking like a statistics idiot (which I am). My main issue is I am not sure what to base the ± confidence interval on. The internal memos use a single number after the '±' (e.g. ±0.9) but the Excel regression analysis does not do it that simply;
Instead, Excel provides the following:
Intcpt -0.2394, 0.0266, -0.2918, -0.1871, -0.2833, -0.1955
Data.X 0.0013, 0.0002,, 0.0009,, 0.0017 ,, 0.0009 ,, 0.0016
I know that the 0.0013 is the slope of the linear trend of monthly observations and to calculate a 10-year trend I would multiply by 120 (months). But it is not clear what numbers I would use to calculate a single '±' that is interpreted as the 95% confidence interval.
Are Excel's 'Lower' and 'Upper' 95% results the basis for the confidence interval? If so, which are the correct numbers to use, and would I then multiply that correct number(s) by 120 as done with the slope coefficient when expressed as a 10-year trend?
Thanks for clearing up an idiot's confusion on this. (Apologize for extra commas used to force columns of data to line up.)