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.

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/
_______

Mark Schreiber wrote:

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

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/
_______

Mark Schreiber wrote:

> Jon,
>
> Thanks for the help. I was aware of the histogram tool. The problem
> is that it is a "snapshot" tool: it does the analysis on the data
> currently in the worksheet. When you change the data, you have to
> completely re-do the histogram. I am looking for a way to set up a
> spreadsheet for recurrent use, so that each month when I input the
> current load profile data, it automatically calculates the histogram.
> Any suggestions?
>
> Mark
>
> "Jon Peltier" wrote:
>
>
>> 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/
>> _______
>>
>> Mark Schreiber wrote:
>>
>>
>>> 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.
>>
>>