I have a correllationMatrix array (1000,1000) elements - this array has been filled using the worksheetfunction.RSQ - so I now want to find the highest RSQ value (negative or posite) - when I know the highest - I then want to find the next highest and so on. So essentially I want a ranking.
xl2007 - Windows 7
xl hates the 255 number
Can you post a sample attachment
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Hi,
Not sure about this...
Assume your data in A1:J10
In L1:
=MAX(A1:J10)
In L2 and copied down,
=MAX(IF($A$1:$J$10<L1,$A$1:$J$10))
It's an array formula.
Good morning
Attached is an example - I have extracted only the relevant code. In this example I write the RSQes to the worksheet "Matrix" - but I really dont want to write the RSQes to a worksheet - what I really want to do is rank the values in then array CorrelMatrix(i + 1, ii + 1) from highest to lowest - ignoring the empty elements. In other words I want to find the value closest to 1 (or -1) - after that I want to find the next highest value and so on. So I am trying to find the pairs of observation that are has the highest correlation.
Also keep in mind this example only has 20 observation - in real cases I will have more than 1K observations.
Ignore the references to ItGrid - it is a third party OCX used as a data grid. I removed the references in this example.
xl2007 - Windows 7
xl hates the 255 number
Application.Large(correllationMatrix, 1)
Application.Large(correllationMatrix, 2)
and so on.
ExcelGeek
Not sure how to use the code example you show here - what does aplication.large return
Last edited by Rasm; 06-18-2011 at 10:26 AM. Reason: typo
xl2007 - Windows 7
xl hates the 255 number
Excelgeek
I got this working - so that is cool - anyway I can get the coordinates of the elements as well - so I know the position in the array where it found the ranked value?
It seems that the WorksheetFunction.Match is the one to use - to find the position in the array - But my array is 20 by 20 - so I cannot get Match to work
Code:Avar=Application.WorksheetFunction.Large(CorrelMatrix, 1)
Last edited by Rasm; 06-18-2011 at 08:13 PM.
xl2007 - Windows 7
xl hates the 255 number
Rasm, please check if this is what you are expecting as a result. If yes, I'll make the macro for this.
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Fox
Yes - the green values is exactly what I am looking for - you can make the matrix single sided - I simply made an mirror image in case it was easier for you -so a pair of Obs 12 & obvs 19 would be the same as Obvs 19 & Obvs 12. I would much appreciated a macro - but keep in mind my real matrix may be may be 1000 by 1000.
xl2007 - Windows 7
xl hates the 255 number
Fox
Yes - the green values is exactly what I am looking for - you can make the matrix single sided - I simply made an mirror image in case it was easier for you -so a pair of Obs 12 & obvs 19 would be the same as Obvs 19 & Obvs 12. I would much appreciated a macro - but keep in mind my real matrix may be may be 1000 by 1000.
xl2007 - Windows 7
xl hates the 255 number
Posting Permissions
Reply With Quote
Bookmarks