histogram from weighted data

  • Thread starter Thread starter mshaffer
  • Start date Start date
M

mshaffer

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'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  :)
Click to expand...

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 :)
 
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


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 :)
 
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.
Click to expand...

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.
 
mshaffer -
... bins that are not populated ... <
Click to expand...

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 ... <
Click to expand...

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. <
Click to expand...

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 ... <
Click to expand...

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



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.
Click to expand...

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.
 
Back
Top