Charting a Frequency Distribution

[Guest]

Greetings, math and Excel wizards! Looking for an idea on how to convert random data into bins of magnitude vs frequency of occurrence, then chart the data. Specifically
1. Data: Electrical demand values for a manufacturing facility, in 15-minute intervals, for an entire month. Data is basically a time-marked stream of kilowatts vs date/time
2. Goal: Create a data set and corresponding chart of kilowatts vs frequency of occurrence. The abscissa will be frequency of occurrence, from 1 (where the single, maximum peak value occurs) to several hundred. The ordinate will be the kilowatt value, or perhaps a bin containing kilowatt values between two values. I envision there will be a single, maximum peak value at the y-axis, then a decreasing function. Mathematically, this is somewhat like a Fourier transform used in vibration analysis, where you take a signal of amplitudes in the time domain and transform it into the frequency domain to identify vibration at significant frequecies

Any suggestions would be greatly appreciated. Thanks.

 

[Jon Peltier]

Mark -

Look at the frequency worksheet function, or at the Histogram tool in
the Analysis Toolpack (Tools menu > Data Analysis).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jon Peltier]

Mark -

You can set up your bins as for the Histogram tool, but use the
Frequency worksheet function to compute your own values. Chart the
calculated values in a column chart. When the monthly values change, the
calculations change, and the chart updates accordingly.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______