Can Excel do most things a Stats program can?

  • Thread starter Thread starter Nev.
  • Start date Start date
N

Nev.

Can Excel do most things a stats program can do? E.g. SPSS, SAS?

Sum of squares easy to use?

Can a reasonably small number of covariates be used without problems?

Where can I find tutorials on such areas?

I saw a discussion on a stats group about what someone wanted to do,
and would like to play around with that in a different field.

TIA,

Nev.
 
LINEST will do multiple linear regression, but Excel versions prior to
2003 can have numerical difficulties. Stats packages will include a
number of diagnostic tests for linear models that are not native to Excel.

Fitting a polynomial trendline on an "XY (Scatter)" chart is incredibly
good (numerically) in all versions of Excel. It is more accurate than
SAS, S-PLUS, R, Minitab, etc. (sorry, I don't use SPSS). If all you
need are the coefficients, and not the diagnostics, ...

You can do ANOVA with LINEST, but only if you know enough about the
underlying linear algebra to force it.

Beyond Linear Models, Excel has very little capability that you would
find in stats packages.

Excel's probability functions are probably adequate for simple
hypothesis testing, but neither as accurate nor as robust (working over
a broad range of input parameters) as what is provided in stats
programs. Excel 2003 is a big improvement, but didn't go far enough.
Ian Smith's VBA library of probability functions
http://members.aol.com/iandjmsmith/examples.xls
is actually better than what is provided in any stats package.

Excel is a reasonable choice to distribute specific calculations (that
are within its capabilities) to a number of users that have Excel, but
do not have (or do not know how to use) stats packages.

If you want to do a variety of analyses and have access to a stats
package, that would be a better choice. If you don't have access to a
stats package, and the cost is prohibitive, consider R,
http://www.r-project.org
which is a free-ware implementation of the S language.

Jerry
 
Excel is a very friendly and convenient platform for displaying data
tables and performing simple summary statistics and graphs. Second
moment statistics like VAR, STDEV, etc. do not have a numerically good
implementation in Excel versions prior to 2003, but you can substitute
DEVSQ(data)/(COUNT(data)-1) for VAR(data), ... to get around this.
Moreover, the numerical problems with older versions will rarely be an
issue in univariate calculations unless you have a very large data set,
or a very small coefficient of variation (called relative standard
deviation in some circles).

LINEST will do multiple linear regression, polynomial fits, etc., but
Excel versions prior to 2003 can have numerical difficulties. Stats
packages will include a number of diagnostic tests for linear (in the
unknowns) models that are not native to Excel.

Fitting a polynomial trendline on an "XY (Scatter)" chart is incredibly
good (numerically) in all versions of Excel. It is more accurate than
SAS, S-PLUS, R, Minitab, etc. (sorry, I don't use SPSS). If all you
need are the coefficients, and not the diagnostics, Excel is great for
this purpose.

You can do ANOVA with LINEST, but only if you know enough about the
underlying linear algebra to force it.

Beyond Linear Models, Excel has very little capability that you would
find in stats packages.

Excel's probability functions are probably adequate for simple
hypothesis testing, but neither as accurate nor as robust (working over
a broad range of input parameters) as what is provided in stats
programs. Excel 2003 is a big improvement, but didn't go far enough.
Ian Smith's VBA library of probability functions
http://members.aol.com/iandjmsmith/examples.xls
is actually better than what is provided in any stats package I know of.

Overall, Excel is a reasonable choice to distribute specific statistical
calculations (that are within its capabilities) to a number of users
that have Excel, but do not have (or do not know how to use) stats packages.

If you want to do a variety of analyses and have access to a stats
package, that would be a better choice. If you don't have access to a
stats package, and the cost is prohibitive, consider R,
http://www.r-project.org
which is a free-ware implementation of the S language.

Jerry
 
can excel plot a graph with the actual data and the data
plus and minus the standard deviation.
thanks
 
Giulio -

If your points represent the average of several readings, and each point
has an associated pair of SDs, use this approach. Put X and Y into two
adjacent columns (X to the left) and put the SD(x) and SD(y) into two
more columns. Make a scatter chart with the X and Y data. Double click
on the series, select X error bars, click in the Custom Plus box and
select the range with the X standard deviation, then click in the Custom
Minus and select the same range. Repeat this for the Y error bars and
the Y standard deviation range.

If you have something like a run chart of individual measurements, you
can add error bars that depict ± N standard deviations from the mean
(you pick N), with the error bars all the same for all points, from mean
- N SD to mean + N SD. Or you could add error bars that show the
standard error in the data; with each point having the same length error
bars, centered on the point's value.

- Jon
 
Back
Top