Contents XY Scatter Plot Simple Regression ... Text Boxes

Elsewhere MS Word Removing duplicate rows

XY Scatter Plot

Plotting X,Y data with anything but scatter plot may produce unintended plots, whereas scatter always plots X,Y data directly
 

Simple Regression

After plotting X,Y data with a scatter plot, right click select one of the rendered point, select the Add Trimline from the pop-up context menu. Typically choose Linear Type. An option for displaying R² (correlation) is available.
 

Analysis Pack

If this is not installed by default, Menu: Tools > Add-Ins ... Sometimes, adding this causes a weird VBA error! Only work around known at the time for this is to uninstall Excel, then reinstall with all options checked (i.e. NOT install on first use).

More Regression - Single

Prepare two columns of data (first row labels optional). Menu: Tools > Data Analysis, Regression, OK. Cell range Y (typically this is main variable), Cell range X (typically this is independent variable), Labels (optional for first row). Enable Residuals, Residual Plots, and other options as necessary, OK.

SUMMARY OUTPUT

Regression Statistics
Multiple R         0.874854404
R Square           0.765370227
Adjusted R Square  0.748610958
Standard Error     2.79954673
Observations      16

ANOVA
           df   SS          MS            F          Significance F
Regression  1  357.9249085 357.9249085	 45.66847192  .00000920184
Residual   14  109.7244665   7.837461895
Total      15  467.649375

         Coefficients   Snd Error   t Stat       P-value      Lower 95%   Upper 95%    Lower 95.0%  Upper 95.0%
Intercept  17.5381565   4.064297893 4.315174962  0.000712329  8.821096734 26.25521627  8.821096734 26.25521627
Mortality   0.324626544 0.04803699  6.757845213  9.20184E-06  0.221597355  0.427655732 0.221597355  0.427655732

RESIDUAL OUTPUT
        Predicted             Standard
Observ  Mortality  Residuals  Residuals
1        53.18      14.12       1.94
2        58.37      -5.87      -0.80
3        72.98      -4.88      -0.67
4        77.46       7.14       0.98
5        77.94     -12.84      -1.76
6        80.77      -8.57      -1.18
7        82.42      -0.72      -0.10
8        84.54       4.66       0.64
9        87.37      -8.47      -1.16
10       89.72      -1.12      -0.15
11       90.90       4.10       0.56
12       92.55      -5.55      -0.76
13       94.20       1.70       0.23
14       95.85       8.65       1.19
15       96.09       4.31       0.59
16       99.16       3.34       0.46

Charts ...

F value large than 10 means the correlation is statistical significant. Associated low p-value further corroborates this.

So for each increase in Mortality, 95% certain Temperature increases between 0.22 and 0.43 . Data points with Stadnard Residual > +/-2 are consider potential outlander data

Regression validity Tests
- straight line (data not curved)
- Residual Data Values when plotted collect around a straight line (using Normal P-Plots)
- Test for presence of Constant Variance (residual vs predicted values)
- passes 'runs' test for elimination of periodic oscillation
 

More Regression - Multiple

Prepare several columns of data (first row labels optional). Menu: Tools > Data Analysis, Regression, OK. Cell range Y (typically this is main variable), Cell range X (typically this is several independent variables - the columsn must be adjacent), Labels (optional for first row). Typically choose Confidence Level of 95%. Enable Residuals, Residual Plots, and other options as necessary, it is best to New Worksheet Ply with a name, OK.
 

More Regression - Multiple Predicting

Here's the deal. Only the main variable is not known ahead of time for a limited range going forward. All independent variables are known into the future. So this turns into an exercise of using the past coefficients to compute the predicted values using already known future independent values.
 

Basic Correlation

Have your equal length data columns ready. Menu: Tools > Data Analysis, Correlation. Select Column data.
 

StatPack

If this is not installed by default, Menu: Tools > Add-Ins, Browse, Open StatPlusV25.xla . If successful a new Menu: StatPlus now present. ...

Normal Probability Plot
 

Fancy Correlation

Have your equal length data columns ready. Menu: StatPlus > Multivariate Analysis > Correlation Matrix. May need to select Use Range References. Select Column data. Output, typcailly new sheet - make sure name is NOT empty. OK.

Results. High Correlation values are typically the most topcial values. Pearson Probabilities (p-value or probability) less than .05, ideally zero, are most topical. Correlation values with corresponding high p-values are unlikely to be statistically relevent.

Text Boxes (Drawing Menu)

Test Boxes can be placed such that they float beside notable areas of worksheet. Easiest way to create a Text Box is to use the Text Box tool on the Drawing Toolbar. May need to adjust View settings if this toolbar is not visible by default.

tablet | printable