Hot answers tagged excel
30
Use the right tool for the right job and exploit the strengths of the tools you are familiar with.
In Excel's case there are some salient issues:
Please don't use a spreadsheet to manage data, even if your data will fit into one. You're just asking for trouble, terrible trouble. There is virtually no protection against typographical errors, wholesale ...
15
They are using different methods to compute the skew. Searching in the help pages for skewness() within the R package e1071 yields:
Joanes and Gill (1998) discuss three methods for estimating skewness:
Type 1:
g_1 = m_3 / m_2^(3/2). This is the typical definition used in many older textbooks.
Type 2:
G_1 = g_1 * sqrt(n(n-1)) / (n-2). Used in SAS and SPSS.
...
15
The exact equation is given in: Venables, W. N. and Ripley, B. D. (2002) Modern Applied Statistics with S. Fourth Edition. Springer-Verlag. I'll give you an example:
### simulate some data with AR(1) where rho = .75
xi <- 1:50
yi <- arima.sim(model=list(ar=.75), n=50)
### get residuals
res <- resid(lm(yi ~ xi))
### acf for lags 1 and 2
...
15
Yes, you can do statistics in Open Office Calc:
There is an add on called R and Calc that allows the user to call R functions from within Open Office.
Here is a list of statistical functions in LibreOffice Calc
Calc's data analysis tool is under development.
However, the spreadsheet interface can get in the way of data analysis, and is often not the ...
14
If you feel you may start more of such very large Excel type projects in the future, then you should consider installing and spending 10 hours learning the basics of R (free), which will let you do what you mention in your question, in a much more efficient manner than Excel.
R for Beginners PDF
You can ask questions about R on StackOverflow and here.
13
An interesting paper about using Excel in a Bioinformatics setting is:
Mistaken Identifiers: Gene name errors
can be introduced inadvertently when
using Excel in bioinformatics, BMC
Bioinformatics, 2004 (link).
This short paper describes the problem of automatic type conversions in Excel (in particular date and floating point conversions). For ...
11
Use LINEST, as shown:
The method is to create new columns (C:E here) containing the variables in the fit. Perversely, LINEST returns the coefficients in the reverse order, as you can see from the calculation in the "Fit" column. An example prediction is shown outlined in blue: all the formulas are exactly the same as for the data.
Note that LINEST is ...
10
I mistrust all but the lowest-level functions in Excel, and for good reason: many procedures that go beyond simple arithmetic operations have flaws or errors and most of them are poorly documented. This includes all the probability distribution functions.
Numerical flaws are inevitable due to limitations in floating point accuracy. For example, no matter ...
10
In R, you can do it using the repcommand:
tab <- data.frame(value=c(1, 2, 3, 4, 5), freq=c(2, 1, 4, 2, 1))
vec <- rep(tab$value, tab$freq)
This gives following result:
> tab
value freq
1 1 2
2 2 1
3 3 4
4 4 2
5 5 1
> vec
[1] 1 1 2 3 3 3 3 4 4 5
For details, see the help file for the repcommand by ...
9
Well, the question whether the paper is correct or biased should be easy: you could just replicate some of their analyses and see whether you get the same answers.
McCullough has been taking different versions of MS Excel apart for some years now, and apparently MS haven't seen fit to fix errors he pointed out years ago in previous versions.
I don't see a ...
9
Although I would always recommend to use R, you could nevertheless achieve what you want with python.
There is at least a package for reading dbf files.
Furthermore, scipy offers a great range of functions for statistical analysis. For example the library ScientifyPython probably contains the functions you need.
The best idea is to check scipy.org. There ...
9
You can import the spreadsheet into R.
Then you can use the function qplot, from the ggplot2 package. It doesn't get much easier than that.
8
The formula for weighted standard deviation is:
$$ \sqrt{ \frac{ \sum_{i=1}^N w_i (x_i - \bar{x}^*)^2 }{ \frac{(M-1)}{M} \sum_{i=1}^N w_i } },$$
where
$N$ is the number of observations.
$M$ is the number of nonzero weights.
$w_i$ are the weights
$x_i$ are the observations.
$\bar{x}^*$ is the weighted mean.
Remember that the formula for weighted mean ...
8
The naive way to calculate the auto correlation (and possibly what Excel uses) is to create 2 copies of the vector then remove the 1st n elements from the first copy and the last n elements from the second copy (where n is the lag that you are computing from). Then pass those 2 vectors to the function to calculate the correlation. This method is OK and ...
8
Typically, a rank $r$ (between $1$ and $n$ for $n$ data) is converted to a percent $p$ via the formula
$$p = 100\frac{r-\alpha}{n+1-2\alpha}$$
for some predetermined "plotting position" $\alpha$ between $0$ and $1$, inclusive. Solving for $r$ in terms of $p$ gives
$$r = (n+1-2\alpha) (p/100) + \alpha.$$
Excel has historically used $\alpha=1$ for its ...
7
I prefer R to Excel for cleaning data. It is certainly possible to do heavy data cleaning in Excel, but I find the mixture of the spreadsheet, Excel equations, and Visual Basic to be tedious and less productive than R.
When I need to repeatedly clean data, I usually write an R function, which only needs to be written once, can be re-run tomorrow or a ...
7
It's wrong because the default behaviour of Excel is highly prominent gridlines, which are distracting and "chartjunky", and it violates the formatting rules for the journal. Journals often have lowest common denominator formatting rules. They're there so that it's harder to screw things up, not because it's the best way to do things. I remember it was ...
7
The short answer is no, there is no easy way to create most of the graphics you mention. But in any graphics environment where you can draw line segments (such as the pen plotter drivers from the 60's, 70's, and 80's), you can construct workable visualizations. So one method is to focus on joined scatterplots (which is the principal mechanism for creating ...
6
Standardization is good, but it's not the right standardization for this situation. It helps to see that multiplying values of RAND() is the same as adding their logarithms (followed by a subsequent exponentiation). Because the different calls to RAND() are supposed to be independent, those logarithms are still independently distributed. As a simple ...
6
Incidently, a question around the use of Google spreadsheets raised contrasting (hence, interesting) opinions about that, Do some of you use Google Docs spreadsheet to conduct and share your statistical work with others?
I have in mind an older paper which didn't seem so pessimist, but it is only marginally cited in the paper you mentioned: Keeling and ...
6
Try trend(known_y's, known_x's, new_x's, const).
Column A below is X. Column B is X^2 (the cell to the left squared). Column C is X^3 (two cells to the left cubed). The trend() formula is in Cell E24 where the cell references are shown in red.
The "known_y's" are in E3:E22
The "known_x's" are in A3:C22
The "new_x's" are in A24:C24
The "const" is ...
6
If by logarithmic regression you mean the model log(y) = m1.x1 + m2.x2 + ... + b + (Error), you can use LOGEST and GROWTH with multiple independent variables. Note that if you want the estimated coefficients m1, m2, ..., b from LOGEST, you'll have to enter the formula into multiple cells as an array. See Excel's online help for the steps required.
...
6
A normal probability plot is an excellent way to compare an empirical distribution to a normal distribution. Its merits are that it clearly displays the nature of any deviations from normality: ideally, the points lie along the diagonal; vertical deviations from the diagonal depict deviations from normality. Its disadvantages are that many people do not ...
6
This web page ("Forest plots : Introduction and explanation") explains how to draw simple forest plots in MS-Excel.
Is your friend conducting a meta-analysis? In that case s/he might be interested in the following two pages:
MIX 2.0 - Meta-Analysis made easy
MetaEasy Excel add-in
6
First, note that your link links to a worked example that will probably help.
To implement the equation in Excel:
make a new column labeled "XY" in E
enter the number of rows in cell "F2" (this will be N)
label column F "X^2"
enter =C2^2 into F2 to caclulate X^2, highlight F2:FN and hit ctrl+D to fill this equation down
enter the equation =(G2*sum(E:E) - ...
6
Number of subjects: 8803 Outcome Variable: Legal Need (Yes or No)
See the end, but I will deal with side issues first.
How to do multiple regression with ... excel?
The easiest way to do multiple regression in Excel is to install the Data Analysis Toolpack, which should have come with Excel but by default isn't installed.
I keep getting ...
6
The standard traditional tool is a histogram. You can do this with the analysis tool pack in Excel, but I'd recommend using a stats package instead.
An extension of the histogram is a line plot showing the density - this is basically your idea of shwoing the bell curve, and it is probably the right one. From here there are various options such as drawing ...
5
This behavior is questionable but documented. The help for "confidence" states:
If standard_dev ≤ 0, CONFIDENCE returns the #NUM! error value.
...
If we assume alpha equals 0.05, we need to calculate the area under the standard normal
curve that equals (1 - alpha), or 95 percent. This value is ± 1.96. The confidence
interval is therefore:
...
5
The papers and other participants point out to technical weaknesses. Whuber does a good job of outlining at least some of its strengths. I personally do extensive statistical work in Excel (hypothesis testing, linear and multivariate regressions) and love it. I use Excel 2003 with a capacity of 256 columns and 65,000 rows which can handle just about 100% ...
5
It sounds like your goal is didactic; that you are trying to explain ordinal logistic to some group of people. I have used Excel for this sort of thing when the topic is much simpler - e.g., crosstabs and chi-square - so that there is some intuition about the formulas.
I don't think that will be the case here. Even if you could find (or create) an Excel ...
Only top voted, non community-wiki answers of a minimum length are eligible
