Finding highest value in array

[Rasm]

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.

[Excel Fox]

Can you post a sample attachment

[Admin]

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.

[Rasm]

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.

[excelgeek]

Application.Large(correllationMatrix, 1)
Application.Large(correllationMatrix, 2)

and so on.

[Excel Fox]

Rasm, please check if this is what you are expecting as a result. If yes, I'll make the macro for this.

[Rasm]

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.

[Rasm]

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.

[Excel Fox]

But I don't see that a pair of Obs are always same. There are some obs which are not following that rule. Is that something wrong with the matrix or is it legit?

[Rasm]

Yes - the matrix is legit - not sure why you can not find reversed pairs - but just set the ShowSingle to True - I properly should not have given the option of making it double sided




If ShowSingle Then
.Cells(ii + 2, i + 2) = CorrelMatrix(i + 1, ii + 1)
.Cells(i + 2, ii + 2) = vbNullString
Else
.Cells(i + 2, ii + 2) = CorrelMatrix(i + 1, ii + 1)
.Cells(ii + 2, i + 2) = CorrelMatrix(i + 1, ii + 1)
End If

[Rasm]

ExcelGeek
Not sure how to use the code example you show here - what does aplication.large return

[Rasm]

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




Avar=Application.WorksheetFunction.Large(CorrelMat rix, 1)

[excelgeek]

Assuming a 1-based array, you could use:

for n = lbound(correlmatrix, 2) to ubound(correlmatrix, 2)
varmatch = application.match(avar, application.index(correlmatrix, 0, n))
if not iserror(varmatch) then
msgbox "x=" & varmatch & "; y=" & n
end if
next n

though I suspect it would be as fast if not faster to simply iterate every element rather than using worksheet functions on arrays.

[Rasm]

Excelgeek
I will try it - but I think you are right about simply using a loop and find it that way instead - it may be faster with the loop - thanks.

[Admin]

Hi Rasm,

It's not a bad idea to write the array in a worksheet, use some formulas and get back the results in an array.

Here is a routing which might help you.


Function SORTMATRIX(ByRef Matrix, ByRef ObvsDown, ByRef ObvsAcross) As Variant

Dim UB1 As Long
Dim UB2 As Long
Dim strMatrix As String
Dim strObvsD As String
Dim strObvsA As String
Dim strMaxVals As String
Dim strRank As String

Dim wksTemp As Worksheet

With Application
.ScreenUpdating = 0
.DisplayAlerts = 0
End With

If TypeOf Matrix Is Range Then Matrix = Matrix.Value
If TypeOf ObvsDown Is Range Then ObvsDown = ObvsDown.Value
If TypeOf ObvsAcross Is Range Then ObvsAcross = ObvsAcross.Value


Set wksTemp = ThisWorkbook.Worksheets.Add

UB1 = UBound(Matrix, 1)
UB2 = UBound(Matrix, 2)

With wksTemp
.Range("b2").Resize(UB1, UB2).Value = Matrix
.Range("a2").Resize(UB1).Value = ObvsDown
.Range("b1").Resize(, UB2).Value = ObvsAcross
.Range("a:c").EntireColumn.Insert
strMatrix = .Range("e2").Resize(UB1, UB2).Address
strObvsD = .Range("d2").Resize(UB1).Address
strObvsA = .Range("e1").Resize(, UB2).Address
strMaxVals = .Range("b2").Resize(UB1).Address
strRank = .Range("c2").Resize(UB1).Address

.Range("b2").Resize(UB1).Formula = "=MAX(INDEX(" & strMatrix & ",0,MATCH(d2," & strObvsA & ",0)))"
.Range("a2").Resize(UB1).Formula = "=INDEX(" & strObvsD & ",MATCH(b2,INDEX(" & strMatrix & ",0,MATCH(d2," & strObvsA & ",0)),0))"
.Range("c2").Resize(UB1).Formula = "=RANK(B2," & strMaxVals & ")+COUNTIF($B$2:B2,B2)-1"

With .Range("a2").Resize(UB1, 3)
.Value = .Value2
.Sort .Cells(1, 2), 2, Header:=2
SORTMATRIX = .Cells(1).Resize(UB1, 2)
End With
End With
wksTemp.Delete

With Application
.ScreenUpdating = 1
.DisplayAlerts = 1
End With

End Function

and call


Sub kTest()

Dim a

a = SORTMATRIX([b2:u21], [a2:a21], [b1:u1])'the range could be CorrelMatrix

End Sub

[Rasm]

Been away - just seen this - I will try it - thanks