Creating a Histogram in Excel: A Step-by-Step Guide for Analyzing Data

In summary, you need to create a chart with the average and standard deviations on the left and right respectively. The chart should have the "Number of Operations" on the y-axis and the "x" should be the length of the object you are measuring.
  • #1
maverick99
74
0
For my lab i have to use my data that i recorded in excel and for that into a chart. The thing is, i have no clue where to even start. The chart is supposed to have the avg at the top of the curve and the standard deviations to the left and right of the avg. On the Y-axis is supposed to be the "Number of Operations" and on the X-axis is supposed to be x(cm). I attached the excel file on here.
Please help me!
 

Attachments

  • physics.zip
    2.7 KB · Views: 187
Physics news on Phys.org
  • #2
To make a chart, click "Insert", "chart" and follow instruction. For the rest, try using "help" on the menu bar, type in "average" etc.
 
  • #3
i already tried messing with the chart millions of times
 
  • #4
Make your leftmost column, your x-axis. If by "number of operations", you mean i, put that next (this will be your y-axis). Highlight those two columns, data only, including the name of the columns. Then "insert", "chart, "XY scatter", next, next, name your axis, "as new sheet", "finish". To type something in the graph (avg, std dev etc.), type in the bar below the menu and press Enter, the text will appear in the graph.
 
  • #5
i already tried doing that. I'm supposed to have a distribution curve thing for the graph. So the top most peak will be my avg and it will look like an upside down V.
 
  • #6
here's the sample model
 

Attachments

  • physics.JPG
    physics.JPG
    23.6 KB · Views: 380
  • #7
Well apparently, your own data doesn't look like that. Your data tells me you measured the length of something 25 times, and that each time you measured it, it was longer than the previous time. Is that what happened? What exactly is the experiment about?
 
  • #8
Gonzolo said:
Well apparently, your own data doesn't look like that. Your data tells me you measured the length of something 25 times, and that each time you measured it, it was longer than the previous time. Is that what happened? What exactly is the experiment about?

Basically had a piece of carbon paper and graph paper on the floor. We rolled a ball off the table 25 times trying to hit this line. We are supposed to measure the distance of the ball hits and graph the average and show the standard deviations.
 
  • #9
Oh I see. Your columns are not right. What you need to do is count the number of "11cm" you have. You have 2, so your first row is then (11,2), then count the number of "12cm", you have 1, so (12,1). Your data points should be:

(11,2)
(12,1)
(13,1)
(14,0)
(15,0)
(16,0)
(17,2)
...
(25,3)
...
(34,1)

You'll have a small bell-curve with this, with a very high std dev..
 
  • #10
Gonzolo said:
Oh I see. Your columns are not right. What you need to do is count the number of "11cm" you have. You have 2, so your first row is then (11,2), then count the number of "12cm", you have 1, so (12,1). Your data points should be:

(11,2)
(12,1)
(13,1)
(14,0)
(15,0)
(16,0)
(17,2)
...
(25,3)
...
(34,1)

You'll have a small bell-curve with this, with a very high std dev..

OOOHHHH, ok cool! Thx a lot man! i owe ya!
 
  • #11
What you really want to do is to create a histogram of your data (frequency distribution). You can do that a few ways. Excel has a data analysis add-in that you should have. If you got to TOOLS...Add-ins and then look for Data Analysis Package and make sure there is a check mark next to it. If you don't, check it and then click OK.

Once you have the add-in you can simply go to TOOLS, Data Analysis. It will bring up a dialog box with a bunch of options, one of which is Histogram.

Once you pick that the histogram, the rest should fall out for you. If you have any problems, look up the histogram tool under the help menu.
 
  • #12
FredGarvin said:
What you really want to do is to create a histogram of your data (frequency distribution). You can do that a few ways. Excel has a data analysis add-in that you should have. If you got to TOOLS...Add-ins and then look for Data Analysis Package and make sure there is a check mark next to it. If you don't, check it and then click OK.

Once you have the add-in you can simply go to TOOLS, Data Analysis. It will bring up a dialog box with a bunch of options, one of which is Histogram.

Once you pick that the histogram, the rest should fall out for you. If you have any problems, look up the histogram tool under the help menu.

thx i will have to try this too!
 

What is normal distribution in excel?

Normal distribution in excel refers to the statistical concept of a bell-shaped curve that represents the spread of data around a central mean value. In excel, it is represented by the NORM.DIST function.

How do I calculate the probability of a data point falling within a certain range in excel?

To calculate the probability of a data point falling within a certain range in excel, you can use the NORM.DIST function. The syntax for this function is NORM.DIST(x, mean, standard_dev, cumulative), where x is the data point, mean is the average of the data, standard_dev is the standard deviation, and cumulative is a logical value that determines whether the function returns the probability of the data being less than or equal to x.

What is the difference between the NORM.DIST and NORM.S.DIST functions in excel?

The NORM.DIST function in excel assumes a mean of 0 and a standard deviation of 1, while the NORM.S.DIST function allows you to specify the mean and standard deviation of your data. This makes the NORM.S.DIST function more versatile and applicable to a wider range of datasets.

Can I create a normal distribution graph in excel?

Yes, you can create a normal distribution graph in excel by using the NORM.DIST function to calculate the probabilities for a range of data points, and then using the charting tools in excel to create a scatter or line graph.

How can I use normal distribution in excel to analyze my data?

Normal distribution in excel can be used to analyze your data by calculating the probabilities of certain data points falling within a range, determining the mean and standard deviation of your data, and creating visual representations of the data distribution using graphs or charts. This can help you draw insights and make informed decisions based on your data analysis.

Similar threads

  • Set Theory, Logic, Probability, Statistics
Replies
4
Views
781
  • MATLAB, Maple, Mathematica, LaTeX
Replies
3
Views
74
  • Other Physics Topics
Replies
1
Views
1K
  • High Energy, Nuclear, Particle Physics
Replies
9
Views
1K
  • Other Physics Topics
Replies
1
Views
2K
  • Introductory Physics Homework Help
Replies
2
Views
935
Replies
24
Views
2K
  • Other Physics Topics
Replies
4
Views
1K
  • STEM Educators and Teaching
Replies
5
Views
546
Replies
10
Views
6K
Back
Top