Creating a frequency matrix in excel

[abberyfarm]

Hi there,

I looking to create a frequency matrix using VBA but I'm not sure if it is possible?

I'd appreciate any advice and code to get me on the way.

I have time, speed and acceleration data for a car in three columns. I'm trying to generate a 2 dimensional frequency matrix of velocity and acceleration.

The frequency matrix would keep track of how often a state occurs in the dataset. At the end of the process every possible state reflected in the dataset would be contained in the matrix indicating how often it occurred.

Here is an example (pictures) of some sample data and how the matrix would look for this small sample. This is secure site.

http://imageshack.us/photo/my-images/683/excel1.png/
http://imageshack.us/photo/my-images/683/excel1.png


You see in the data for example state (1, -1.1) occurred twice and state (2,-0.8) occurred once etc.

I suppose you would start off by
1. Finding the last row of data.
2. Find the max speed value and create matrix heading 0 - Max value at 1 km/hr intervals.
3. Find the min and max values of acceleration and create matrix heading from Min - Max at 0.1 m/s intervals.
4. Start the top of the list and go down through each set of points (speed, acceleration) in the data then go to the matrix and insert 1.
5. Then continue onto the next point and if a previous point occurred then increase the value in the matrix by 1.

If I need to explain in more detail, please let me know

Appreciate any input

Thanks

John

 

[VoG]

Duplicate of http://www.mrexcel.com/forum/showthread.php?t=576628

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).