Questions tagged [excel]

Microsoft Excel is a commercial spreadsheet program. Use this tag for any on-topic question that (a) involves Excel either as a critical part of the question or expected answer, & (b) is not just about how to use Excel.

Excel is a commercial spreadsheet program created by Microsoft. It can conduct mathematical and statistical analyses (such as linear regression) and produce charts and graphics to visualize data (such as scatterplots and line, bar, and pie charts).

Frequently it is recommended to avoid conducting statistical analysis in Excel, as it is known to have issues with numerical precision in computing statistical distributions and with the production of random numbers (see McCullough & Heiser, 2008; Yalta, 2008). Also, calculating directly within cells and copying and pasting values (as opposed to manipulating data through documented syntax) can produce errors when using spreadsheet software.

Frequently Excel's default charts are criticized as well (see Su, 2008). But Excel does have the capabilities to produce clear and effective graphics. The blog of Jon Peltier has many examples.

Citations

McCullough B.D. & Heiser David A.. 2008. On the accuracy of statistical procedures in Microsoft Excel® 2007. Computational Statistics & Data Analysis 52(10): 4570-4578.

Su Yu-Sung. 2008. It's easy to produce chartjunk using Microsoft® Excel 2007 but hard to make good graphs. Computational Statistics & Data Analysis 52(10): 4594-4601.

Yalta A. Talha. 2008. The accuracy of statistical distributions in Microsoft® Excel 2007. Computational Statistics & Data Analysis 52(10): 4579-4586.

420 questions
85
votes
7 answers

Line of best fit does not look like a good fit. Why?

Have a look at this Excel graph: The 'common sense' line-of-best-fit would appear be an almost vertical line straight through the center of the points (edited by hand in red). However the linear trend line as decided by Excel is the diagonal black…
ConanTheGerbil
  • 921
  • 1
  • 6
  • 4
52
votes
8 answers

Excel as a statistics workbench

It seems that lots of people (including me) like to do exploratory data analysis in Excel. Some limitations, such as the number of rows allowed in a spreadsheet, are a pain but in most cases don't make it impossible to use Excel to play around with…
Carlos Accioly
  • 4,715
  • 4
  • 25
  • 28
35
votes
6 answers

How do I calculate a weighted standard deviation? In Excel?

So, I have a data set of percentages like so: 100 / 10000 = 1% (0.01) 2 / 5 = 40% (0.4) 4 / 3 = 133% (1.3) 1000 / 2000 = 50% (0.5) I want to find the standard deviation of the percentages, but weighted for their…
Yahel
  • 555
  • 3
  • 9
  • 11
21
votes
3 answers

How to check for normal distribution using Excel for performing a t-test?

I want to know how to check a data set for normality in Excel, just to verify that the requirements for using a t-test are being met. For the right tail, is it appropriate to just calculate a mean and standard deviation, add 1, 2 & 3 standard…
Eudora
  • 281
  • 1
  • 2
  • 4
21
votes
3 answers

What is the name of this plot that has rows with two connected dots?

I've been reading EIA report and this plot captured my attention. I now want to be able to create the same type of plot. It shows the energy productivity evolution between two years (1990-2015) and adds the change value between this two…
Otto
  • 313
  • 2
  • 6
17
votes
4 answers

How would one graph the results of subjective rank order?

I'm looking for way to visualize subjective rankings, separate from my non-parametric tests. I've asked 12 participants to rank 8 different items according to different subjective criterion (separate rankings for each one). For any individual set of…
Jesse
  • 171
  • 1
  • 1
  • 4
16
votes
1 answer

Why do Excel and WolframAlpha give different values for skewness

for the following 3 values 222,1122,45444 WolframAlpha gives 0.706 Excel, using =SKEW(222,1122,45444) gives 1.729 What explains the difference?
16
votes
1 answer

I have a line of best fit. I need data points that will not change my line of best fit

I'm giving a presentation about fitting lines. I have a simple linear function, $y=1x+b$. I'm trying to get scattered data points that I can put in a scatter plot that will keep my line of best fit the same equation. I'd love to learn this…
Ryan Chase
  • 613
  • 1
  • 4
  • 15
15
votes
3 answers

Best open source data visualization software to use with PowerPoint

What is the best open source data visualization software? I require the following: Can import data from Microsoft Excel (importing data from Oracle databases would be good too, but this is not mandatory). Charts generated by the software can be…
R Learner
  • 479
  • 4
  • 12
14
votes
2 answers

How to convert a frequency table into a vector of values?

Using R or Excel, what is the easiest way to convert a frequency table into a vector of values? E.g., How would you convert the following frequency table Value Frequency 1. 2 2. 1 3. 4 4. 2 5. 1 into the…
Rob
  • 391
  • 1
  • 3
  • 8
14
votes
2 answers

Formula for autocorrelation in R vs. Excel

I am trying to figure out how R computes lag-k autocorrelation (apparently, it is the same formula used by Minitab and SAS), so that I can compare it to using Excel's CORREL function applied to the series and its k-lagged version. R and Excel (using…
Galit Shmueli
  • 1,090
  • 8
  • 10
13
votes
1 answer

How to stop excel from changing a range when you drag a formula down?

I'm trying to normalize a set of columns of data in an excel spreadsheet. I need to get the values so that the highest value in a column is = 1 and lowest is = to 0, so I've come up with the formula: =(A1-MIN(A1:A30))/(MAX(A1:A30)-MIN(A1:A30)) This…
Omar Kooheji
  • 249
  • 1
  • 2
  • 7
12
votes
3 answers

What is the best way to Reshape/Restructure Data?

I am a research assistant for a lab (volunteer). I and a small group have been tasked with the data analysis for a set of data pulled from a large study. Unfortunately the data were gathered with an online app of some sort, and it was not programmed…
Wilkoe
  • 139
  • 1
  • 5
11
votes
2 answers

Use a trendline formula to get values for any given X with Excel

Is there an easy way to apply the trend line formula from a chart to any given X value in Excel? For example, I want to get the Y value for a given X = $2,006.00. I've already taken the formula and retyped it out be: =-0.000000000008*X^3 -…
Kirk Ouimet
  • 211
  • 1
  • 2
  • 7
11
votes
6 answers

Quartiles in Excel

I am interested in the definition of quartile that is usually used when you're in basic statistics. I have a Stat 101 type book and it just gives an intuitive definition. "About one quarter of the data falls on or below the first quartile..." …
GeoffDS
  • 752
  • 3
  • 6
  • 15
1
2 3
27 28