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.

442 questions
14
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…
4
votes
1 answer

Recovering data from a poorly-formatted source

I've been given a stack of demographic data to look over by the small nonprofit I work for, though as the original electronic files have been misplaced by a predecessor I'm having to reverse-engineer it from a strangely-formatted hardcopy. The data…
2
votes
2 answers

How to apply a logarithmic trendline if X Values = 0?

OK, I know its not possible because Log(0) is undefined. But here is my Problem: I measured a Weight gain of my Test object's over Time. Test objects, made of nonwoven material, were placed in very humid Atmosphere (enclosed above a Tank of heated…
2
votes
1 answer

Change in arrival from year to year

I am using to Excel to analyze a dataset I have. I'm looking at bird migration. I have the date the birds were seen and the number of birds (abundance). I have a dataset of 10 years and I am trying to see if there is a shift in arrival time from…
2
votes
3 answers

Calculate relative price levels in 4 markets?

I am working in Excel and want to calculate the relative price level in 4 markets. I know a good method I can use when I am only looking at 2 markets USA UK Bubble gum 4 6 Lollypop 3 Chocolate bar 7 8 In the above…
Jake
  • 21
2
votes
1 answer

Overcoming Excel size limit to analyse a large network usage data

I am analysing CPU useage on a large network. In order to do that, I was provided with a large excel sheet. It contains batchID (means we dedicate a CPU to run that task) startTime, endTime (means we know the CPU is fully occupied during this…
Dean
  • 121
  • 1
1
vote
4 answers

Removing empty columns from a dataset

I am taking a class in data mining and I am working on a term project using the BRFSS dataset. I have a huge dataset with 405 columns and 12,000 rows. There are many columns which are completely empty. I was trying to remove empty columns using SAS,…
user3897
  • 527
1
vote
1 answer

How to assess whether a set of cells all have a specified value in Excel

I have collected a bunch of statistical results in the form of "YES" and "NO" strings. Now I would like to have a summary cell displaying "YES" if all these cells equal "YES" (or are empty).
aioobe
  • 113
1
vote
3 answers

Data exploration tools for Excel

I have a sample of records, with string and numerical columns. My sample is currently hosted in an excel spreadsheet. I need a tool that wil produce discripte statistics for each colomn, such as max and min values, number of unique values, max…
entropy
  • 1,242
1
vote
1 answer

Documentation for SKEW.P

Original question is here. I think the formula for population skewness from photo here is wrong. I think correct formula is this. Am i right? [EDIT] Please check the example and the result in documentation with both formulas.
Human
  • 13
1
vote
1 answer

Excel how to distribute 2 percentage fields between 3 fields

I'm having a difficulty to figure out how to distribute 2 percentages fields in 3. Let's say we have 2 teams. I think they both have chances 50/50 means that the draw has the percent of 50, 25 for the first team and 25 for the second team. So i'm…
Zaur
  • 13
1
vote
1 answer

An Alternative to the tdist() Function in Excel for Google Spreadsheets?

I'm using Google Docs for a statistics class and my instructor suggested we use the "tdist()" function in Excel. http://office.microsoft.com/en-us/excel-help/tdist-HP005209312.aspx I can't find anything similar in Google…
timothy.s.lau
  • 1,113
  • 3
  • 14
  • 28
0
votes
2 answers

How to digitize an image graph to excel data?

I'm trying to compare my model results with an experiment. I want to overlap the model curve with experiment curve from a paper. What are my options for digitizing the graph from an image in the pdf paper to excel data?
user40
  • 113
0
votes
0 answers

Calculate item and customer wise distribution list based on min and max

I initially tried to do this directly in SQL Server but it seems like it can't be possible through query so I want to calculate this "Distribute" column in Excel. Below is the details of the question. Appreciate if someone can help here. I have…
0
votes
0 answers

T-test. Whether to assume equal or unequal variance

Is it acceptable to assume unequal variance for my data to perform a t-test. If so, what is a justification for this?
1
2