Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
histogram from weighted data
I'm trying to use the 2007 histogram tool (or frequency) to show me
the frequency of data, but my data is in a different form than required for either of these tools. Let me use the following simple case -- to show the frequency of student grades both tools assume I have a list of student's test grades. What if I instead have more than one column, e.g., "grade" and "number students with that grade"?? My application is actually different, but it's still 2 columns, a numeric property and a weighting factor (e.g., fraction of the total). TIA :) |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
histogram from weighted data
On Jan 12, 1:27*pm, mshaffer wrote:
I'm trying to use the 2007 histogram tool (or frequency) to show me the frequency of data, but my data is in a different form than required for either of these tools. Let me use the following simple case -- to show the frequency of student grades both tools assume I have a list of student's test grades. What if I instead have more than one column, e.g., "grade" and "number students with that grade"?? My application is actually different, but it's still 2 columns, a numeric property and a weighting factor (e.g., fraction of the total). TIA *:) I've confused at least one person, so let me de-simplify ... For more than 10,000 particles, I have for each particle: (1) its calculated density and (2) its mass (relative to the whole, or absolute, take your pick ... keep in mind these particles vary in size and composition, so some particles can have the same mass but different density). I need to plot the mass distribution according to binned density values. TIA :) |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
histogram from weighted data
mshaffer -
Here's one way to create the histogram from frequency data: Arrange data with Grades in a column on the left and Counts in an adjacent column on the right. Enter labels "Grade" and "Count" in top row. Select any cell in the Grade or Count column. Choose Insert PivotTable (dropdown) | PivotChart. Create PivotTable with PivotChart, OK. In the Field List, do not check the box for Grade; instead, click Grade and drag to the Axis Fields area (or to the Row Labels area). Then click the box for Count. Select one of the Grade values (i.e., one of the Row Labels) in the table, right-click, and choose Group. Specify "nice values" for Start, End, and By (the step or interval width for the frequency distribution). Optionally, click one of the bars of the Column chart to select the data series, right-click, choose Format Data Series | Series Options, change the Gap Width to No Gap, and Close. - Mike http://www.MikeMiddleton.com "mshaffer" wrote in message ... I'm trying to use the 2007 histogram tool (or frequency) to show me the frequency of data, but my data is in a different form than required for either of these tools. Let me use the following simple case -- to show the frequency of student grades both tools assume I have a list of student's test grades. What if I instead have more than one column, e.g., "grade" and "number students with that grade"?? My application is actually different, but it's still 2 columns, a numeric property and a weighting factor (e.g., fraction of the total). TIA :) |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
histogram from weighted data
On Jan 12, 5:34*pm, "Mike Middleton" wrote:
mshaffer *- Here's one way to create the histogram from frequency data: Arrange data with Grades in a column on the left and Counts in an adjacent column on the right. Enter labels "Grade" and "Count" in top row. Select any cell in the Grade or Count column. Choose Insert PivotTable (dropdown) | PivotChart. *Create PivotTable with PivotChart, OK. In the Field List, do not check the box for Grade; instead, click Grade and drag to the Axis Fields area (or to the Row Labels area). Then click the box for Count. Select one of the Grade values (i.e., one of the Row Labels) in the table, right-click, and choose Group. Specify "nice values" for Start, End, and By (the step or interval width for the frequency distribution). Optionally, click one of the bars of the Column chart to select the data series, right-click, choose Format Data Series | Series Options, change the Gap Width to No Gap, and Close. Thanx Mike for your response! ... That comes very close to what I want. However, in the little time I've played with it, I discovered at least one problem. The histogram tends to not present bins that are not populated, so one distribution cannot be visually compared with another. Also, my meager experience with pivot tables and v.2003 led me to the impression that pivots are for presentation only. IE, correct me if I'm wrong, but it's practically impossible if I actually wanted to use the pivot table as a data source for something else. Still, I'm now using Excel 2007, and I probably should re-aquaint with pivots. Any sources of pivot turorials that you'd believe woud help me would be extremely appreciated. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
histogram from weighted data
mshaffer -
... bins that are not populated ... < For such a bin, add a value that would be in that bin with a Count zero to your original data. Then use the same steps. ... pivots are for presentation only ... < I think the pivot table feature is very useful for both analysis and presentation. ... it's practically impossible if I actually wanted to use the pivot table as a data source for something else. < The "intelligence" embedded within a pivot table does sometimes get in the way of doing simpler things. I sometimes use a pivot table result by copying and Paste Special Values (thereby eliminating the built-in "intelligence") so that I can prepare my own chart or even use the results as the data for another pivot table. ... sources of pivot turorials ... < Use Google or other search engine for "excel pivot table tutorial" or "excel 2007 pivot table tutorial" (without the quotes). - Mike http://www.MikeMiddleton.com "" wrote in message ... On Jan 12, 5:34 pm, "Mike Middleton" wrote: mshaffer - Here's one way to create the histogram from frequency data: Arrange data with Grades in a column on the left and Counts in an adjacent column on the right. Enter labels "Grade" and "Count" in top row. Select any cell in the Grade or Count column. Choose Insert PivotTable (dropdown) | PivotChart. Create PivotTable with PivotChart, OK. In the Field List, do not check the box for Grade; instead, click Grade and drag to the Axis Fields area (or to the Row Labels area). Then click the box for Count. Select one of the Grade values (i.e., one of the Row Labels) in the table, right-click, and choose Group. Specify "nice values" for Start, End, and By (the step or interval width for the frequency distribution). Optionally, click one of the bars of the Column chart to select the data series, right-click, choose Format Data Series | Series Options, change the Gap Width to No Gap, and Close. Thanx Mike for your response! ... That comes very close to what I want. However, in the little time I've played with it, I discovered at least one problem. The histogram tends to not present bins that are not populated, so one distribution cannot be visually compared with another. Also, my meager experience with pivot tables and v.2003 led me to the impression that pivots are for presentation only. IE, correct me if I'm wrong, but it's practically impossible if I actually wanted to use the pivot table as a data source for something else. Still, I'm now using Excel 2007, and I probably should re-aquaint with pivots. Any sources of pivot turorials that you'd believe woud help me would be extremely appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Continuous data in a histogram | Excel Discussion (Misc queries) | |||
I was creating a histogram; now i have data but no histogram. | Excel Worksheet Functions | |||
HOW CAN I CREATE A HISTOGRAM GRAPH FOR MY DATA? | Excel Worksheet Functions | |||
HOW CAN I CREATE A HISTOGRAM GRAPH FOR MY DATA? | Excel Worksheet Functions | |||
How to Histogram w/o raw data but histogram Table on Excel | Charts and Charting in Excel |