[phillip harrison]

Hi there, I've got, what i think it quite a tough problem to crack, I
have some idea of how to go about it but any help would be more than
greatly appreciated.

ok.. what i have is this:

2 columns of data, one starts at C3 and the other at D3, the first
contains 'mean' values and the other 'range' values (im just mentioning
this so that i can refer to and distinguish between the two columns via
these names) The column lenghts, although always equal to each other,
shall vary each time the macro is run.

I then have a matrix, on the y axis is the mean and the range is on the
x axis. The y axis increments in steps of 20 (up to a max of 300) and
the x axis in steps of 10 (up to a max of 600) (i.e. the first cell of
the matrix at the bottom left hand corner will have: bottom line = mean
0, top line = mean 20, left side line = range 0, right side line = range
10)... to try and clarify this incase its still confusing, as you cant
label the actual lines on an excel spreadsheet I have to label the cells
to the left and below our cell in the bottom left hand corner of the
matrix. The left hand cell will contain the number 10 and the bottom
cell will contain the number 5, these as you prob have noticed are half
way between the X and Y increments, becuase as i said before you cant
label the actual lines of cells only the cells themselves which I take
to represent the meridian of the cells.

Ok.. hopefully that makes sense.. thats the setup I have. Now, what i
need to do is this:

Go through each of the mean and range value pairs in the columns and
decide which of the cells they belong to in the matrix, for example if
the mean = 3.48 and the range = 6.4 then i shall put a 1 in the cell
with y value (mean) = 10 and x value (range) = 5, this is because 3.48
is between 0 and 20, and 6.4 is between 0 and 10. If another mean and
range pair fall into the same cell then the 1 needs to be changed to a 2
and so on.

I really hope this makes sense and that someone out there knows how to
do this and can let me know please.

Thank you

Phillip

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[shockley]

Phillip,

I spent a little time on your project, not because I really wanted to,
rather because I'm never able to stop once I start a programming thing...

Here's what I came up with: http://www.frontiernet.net/~wily11/Matrix.xls

It's not commented, so if you have any questions, feel free to email me at
my personal email.

HIH,
Larry




"phillip harrison" wrote in message
...
Hi there, I've got, what i think it quite a tough problem to crack, I
have some idea of how to go about it but any help would be more than
greatly appreciated.

ok.. what i have is this:

2 columns of data, one starts at C3 and the other at D3, the first
contains 'mean' values and the other 'range' values (im just mentioning
this so that i can refer to and distinguish between the two columns via
these names) The column lenghts, although always equal to each other,
shall vary each time the macro is run.

I then have a matrix, on the y axis is the mean and the range is on the
x axis. The y axis increments in steps of 20 (up to a max of 300) and
the x axis in steps of 10 (up to a max of 600) (i.e. the first cell of
the matrix at the bottom left hand corner will have: bottom line = mean
0, top line = mean 20, left side line = range 0, right side line = range
10)... to try and clarify this incase its still confusing, as you cant
label the actual lines on an excel spreadsheet I have to label the cells
to the left and below our cell in the bottom left hand corner of the
matrix. The left hand cell will contain the number 10 and the bottom
cell will contain the number 5, these as you prob have noticed are half
way between the X and Y increments, becuase as i said before you cant
label the actual lines of cells only the cells themselves which I take
to represent the meridian of the cells.

Ok.. hopefully that makes sense.. thats the setup I have. Now, what i
need to do is this:

Go through each of the mean and range value pairs in the columns and
decide which of the cells they belong to in the matrix, for example if
the mean = 3.48 and the range = 6.4 then i shall put a 1 in the cell
with y value (mean) = 10 and x value (range) = 5, this is because 3.48
is between 0 and 20, and 6.4 is between 0 and 10. If another mean and
range pair fall into the same cell then the 1 needs to be changed to a 2
and so on.

I really hope this makes sense and that someone out there knows how to
do this and can let me know please.

Thank you

Phillip

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mark D[_3_]]

Seems simple enough..

If you say that the Mean data is always found in column C and the range data
always in column D we can loop through those columns to determine how many
values there are, its then a simple matter of putting the data into the
matrix.

If we say that the values of the matrix y axis are in column G (G2=300, G3 =
280 ... G16=20) and that the values of the matrix x axis are in row 17
(H17=10, H18=20 ... BO17=600) then the following code could be applied.

[Commented Version]

Sub Calc_Data()
Application.ScreenUpdating = False
' Turn off the screen updating so that it process that little bit quicker
Sheet1.Range("H2:BO16").Clear
' Clear the current data
Range("A1").Select
' Reselect the first corner cell
iCount = 3
' Start at 3 as 3 is the first row containing data
Do
iCount = iCount + 1
' Increment the row each time we pass through the loop
Loop Until IsEmpty(Sheet1.Cells(iCount, 3).Value)
' When we come to an empty cell exit the loop
iCount = iCount - 1
' Now deduct one from the loop value to find the last cell containg data
'
For iloop = 3 To iCount
iRow = 16 - Int(Sheet1.Cells(iloop, 3).Value / 20)
' Work out where the mean value falls
iCol = 8 + Int(Sheet1.Cells(iloop, 4).Value / 10)
' work out where the range value falls
'
Sheet1.Cells(iRow, iCol).Value = Sheet1.Cells(iRow, iCol).Value + 1
' Write the data to the table, if the cell already contains a value then
increment it by one.
Next iloop
Application.ScreenUpdating = True
' Turn the screen updating back on..
End Sub

This would then populate the table according to the number of occurences.

[Uncommented Version - might be easier to read]
Sub Calc_Data()
Application.ScreenUpdating = False
Sheet1.Range("H2:BO16").Clear
Range("A1").Select
iCount = 3
Do
iCount = iCount + 1
Loop Until IsEmpty(Sheet1.Cells(iCount, 3).Value)
iCount = iCount - 1
For iloop = 3 To iCount
iRow = 16 - Int(Sheet1.Cells(iloop, 3).Value / 20)
iCol = 8 + Int(Sheet1.Cells(iloop, 4).Value / 10)
Sheet1.Cells(iRow, iCol).Value = Sheet1.Cells(iRow, iCol).Value + 1
Next iloop
Application.ScreenUpdating = True
End Sub

btw - if thats not what you meant just drop me a line..

Mark D



"phillip harrison" wrote in message
...
Hi there, I've got, what i think it quite a tough problem to crack, I
have some idea of how to go about it but any help would be more than
greatly appreciated.

ok.. what i have is this:

2 columns of data, one starts at C3 and the other at D3, the first
contains 'mean' values and the other 'range' values (im just mentioning
this so that i can refer to and distinguish between the two columns via
these names) The column lenghts, although always equal to each other,
shall vary each time the macro is run.

I then have a matrix, on the y axis is the mean and the range is on the
x axis. The y axis increments in steps of 20 (up to a max of 300) and
the x axis in steps of 10 (up to a max of 600) (i.e. the first cell of
the matrix at the bottom left hand corner will have: bottom line = mean
0, top line = mean 20, left side line = range 0, right side line = range
10)... to try and clarify this incase its still confusing, as you cant
label the actual lines on an excel spreadsheet I have to label the cells
to the left and below our cell in the bottom left hand corner of the
matrix. The left hand cell will contain the number 10 and the bottom
cell will contain the number 5, these as you prob have noticed are half
way between the X and Y increments, becuase as i said before you cant
label the actual lines of cells only the cells themselves which I take
to represent the meridian of the cells.

Ok.. hopefully that makes sense.. thats the setup I have. Now, what i
need to do is this:

Go through each of the mean and range value pairs in the columns and
decide which of the cells they belong to in the matrix, for example if
the mean = 3.48 and the range = 6.4 then i shall put a 1 in the cell
with y value (mean) = 10 and x value (range) = 5, this is because 3.48
is between 0 and 20, and 6.4 is between 0 and 10. If another mean and
range pair fall into the same cell then the 1 needs to be changed to a 2
and so on.

I really hope this makes sense and that someone out there knows how to
do this and can let me know please.

Thank you

Phillip

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[phillip harrison]

Guys

Thanks ever so much for the detailed responces, they work a treat....
only think now is to write a document on how the programs work :o)

Thanks again.

Phillip

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[shockley]

Phillip,

I hope I didn't make it too easy for you! Using F8 to step through the
program line by line will help you understand how it works. Good luck!

Larry




"phillip harrison" wrote in message
...


Guys

Thanks ever so much for the detailed responces, they work a treat....
only think now is to write a document on how the programs work :o)

Thanks again.

Phillip

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!