Note: I am only addressing your questions about the Data Analysis formulas. I am not addressing whether a linear regression is the best method of analyzing your data.

Download the "data analysis formulas.xlsx" (click here). Ignore any preview errors and request to log in. Just click Download.

Refer to the following image (part of the "corrections" worksheet) and formulas and explanation below:

Key corrections and additions:

R33, observations: =COUNT(J8:J10000)

L8:L372, predicted y: =I8*$R$43 + $R$42

U29, mean x: =AVERAGE(I8:I10000)

U30, mean y: =AVERAGE(J8:J10000)

Key formulas for coefficient std err, t-stat and p-value:

S42, intercept std error:

=SQRT(SUM((J8:J10000 - L8:L10000)^2) / (R33 - 2)) * SQRT(1/R33 + U29^2 / SUM(IF(I8:I10000<>"", I8:I10000 - U29)^2))

S43, slope std error:

=SQRT(SUM((J8:J10000 - L8:L10000)^2) / (R33 - 2)) / SQRT(SUM(IF(I8:I10000<>"", I8:I10000 - U29)^2))

T42:T43, t-stat: =R42/S42

U42:U43, p-value: =T.DIST.2T(ABS(T42), $R$33 - 2)

Note: S42 and S43 must be array-entered (press ctrl+shift+Enter) in some versions of Excel.

(-----)

Your major mistakes were in the calculation observations (R33) and apparently the predicted-y values (L8:L372).

In R33, your formula was =COUNTIF(C:C,">0"). That caused you to ignore 3 rows where AD=0.

But of course, Data Analysis includes those rows.

The predicted-y values (and therefore the error and error^2 values) were incorrect.

But since you neglected to show the formulas in L8:L372 (and M8:M372 and N8:N372), I don't know what you did wrong.

That is why your attempt to use my coefficient std error formulas did not match the Data Analysis.

Also note that I altered the std error formulas to allow for the entire range of rows 8:10000, which you seem to want.

(-----)

Your p-value formula was also incorrect. You used T.TEST instead of TDIST (old style).

But note that I updated the t-dist calculation to use T.DIST.2T.

Note that T.DIST (new style) is wrong for this purpose. See the help pages for an explanation.

(-----)

Instead of explaining the other corrections and improvements one-by-one, I'll leave it up to you to review all of the green-highlighted cells and ask any questions that you might have.

But I do want highlight some technical errors, even though your values were correct.

Multiple R (R29) should be calculated as SQRT(R30), where R30 is R Squared, which should be calculated using RSQ.

You used CORREL in R29, which can return values between -1 and 1.

But Multiple R should always be non-negative, according to one of the references that I cited in my first response.

(-----)

All right, that is all I have time for. LMK if you have any questions.