histogram from weighted data

[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

[shg]

What if I instead have more than one column, e.g., "grade" and "number students with that grade"??

That is a histogram.

My application is actually different, but it's still 2 columns, a numeric property and a weighting factor (e.g., fraction of the total).

So is that; it's just normalized to percentages.

[mshaffer]

That is a histogram.
...
So is that; it's just normalized to percentages.

Not actually ... I'll provide exactly what I have, and what I need ...

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

[shg]

I'll provide exactly what I have, and what I need ...

What you have is a workbook. Let's start with that.

[mshaffer]

What you have is a workbook. Let's start with that.

Perfectly alright by me ... (not to be a smartass, but ...) what else do you need to be of some help?

TIA

[shg]

The workbook.

[mshaffer]

The workbook.

Thanx for taking a look!

I've created a subset of the particle list ... ~1000 particles all with their density and wt contribution to the whole. I'm trying to bin the data in density increments of (eg) ..., 2.5, 2.6, 2.7, ..., 6.0 relative to the particle mass. In other words, to see the distribution of mass relative to density. For example, if most of the particles are quartz, then the mass would be weighted towards low density, or the opposite if most were magnetite. In fact however, most of the particles are a combination of both and differ in size.

xls attached

TIA

[shg]

List the lumbers 2.5, 2.6, ... 5.4 down a column, say E2:E31.

Select F2:F31 and paste =FREQUENCY(B2:B1002, E2:E31) in the formula bar. DON'T press ENter. Instead, press and hold the Ctrl and Shift keys, then press Enter.

Plot those two columns.

[shg]

See attached.

[mshaffer]

See attached.

Thanx SHG ... exactly what I need ...

However, before I declare problem solved, we ought to clarify that your previous message only builds the frequency distribution, and to get to what I want you employed 2 more calculations and an ingenous use of

'=[previous cell]/ ROWS(tbl)',

followed by

'= SUMPRODUCT( (tblDens > [bin value]) * (tblDens <= [bin value]) * tblWgt)'

... and as well failed to mention the latter being entirely dependent on having sorted the original table (... and I wonder why sorting is needed?).

I've been able to duplicate everything but I do have a couple of questions regading how you labelled the tables ... and I'd turn to Excel help, but I'm not sure what to search for. For example does "tblDens" simple represent the range B2:B1002? How did you get values for "% of particles" (=F2 / ROWS(tbl)" to return % without '*100'?

Thanx again for everything ... good job!!

[shg]

(... and I wonder why sorting is needed?).

It's not. You can re-sort by particle ID to restore the orginal order without affecting the results.

For example does "tblDens" simple represent the range B2:B1002?

Yes. There's a dynamic named ranged tbl (do Insert > Name > Define to see the definition), and tblDens is defined as one of the columns of that table.

How did you get values for "% of particles" (=F2 / ROWS(tbl)" to return % without '*100'?

Percent means "per 100": 5% = 5/100 = 0.05. In Excel, the difference between 0.05 and 5% is formatting; the numbers are the same.

[mshaffer]

Altho it leaves the subject of this topic, I've suceeeded in duplicating what you've done with a real collection of data, but (at the risk of appearing dumb) I don't understand a couple of things:

(1) I've searched help for "Ctl+Shift+Enter", but I cannot find a reference to any context of its use or what it does. Does it simply fill multiple cells as if I had created a single cell and then drag-filled the remaining?

(2) I've also created names for my columns, but they seem mis-behaved, and I notice your definitions for names are different. For example "tblDens":

Mine: $B$4:$B$22400
Yours: =INDEX(tbl, 0, 2) ... let me ask why & how? ...

(3) I cannot find an example of how you used SUMPRODUCT. That is, the example in Excel help shows a comma separating the arrays to be multiplied & added. There's obviously some logic in your argument that I'm missing.

once again ... TIA

[shg]

(1) http://www.cpearson.com/excel/ArrayFormulas.aspx

(2)

There's a dynamic named ranged tbl (do Insert > Name > Define to see the definition), and tblDens is defined as one of the columns of that table.

See the definition of tbl.

(3) http://xldynamic.com/source/xld.SUMPRODUCT.html

[mshaffer]

There's a dynamic named ranged tbl (do Insert > Name > Define to see the definition), and tblDens is defined as one of the columns of that table.

Forgive my density, but I've been accessing how you defined your names by using the 'Formulas' tab => 'Name Manager', and I cannot find anything akin to "Name" via the 'Insert' tab. That is, I never did find any other options for "do Insert" other than inserting cells via right-clik.

Thanx for the other links ... I'm off to do further reading

Edit: In the end it seems that your implimentation of the function SUMPRODUCT did everything I needed (ie, without any need for FREQUENCY or HISTOGRAM. Still, I am seeing inconsistent results from my own implimentations, but further questions regarding this function will be a new topic.