Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Finding highest value in array

  1. #1
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14

    Finding highest value in array

    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

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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.

  4. #4
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    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.
    Attached Files Attached Files
    xl2007 - Windows 7
    xl hates the 255 number

  5. #5
    Junior Member
    Join Date
    Apr 2011
    Posts
    6
    Rep Power
    0
    Application.Large(correllationMatrix, 1)
    Application.Large(correllationMatrix, 2)

    and so on.

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10

    Find Highest Value In Array Grid

    Rasm, please check if this is what you are expecting as a result. If yes, I'll make the macro for this.
    Attached Files Attached Files
    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

  7. #7
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    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

  8. #8
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    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

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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?
    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

  10. #10
    Senior Member
    Join Date
    Apr 2011
    Posts
    190
    Rep Power
    14
    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

    Code:
    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
    xl2007 - Windows 7
    xl hates the 255 number

Similar Threads

  1. Find the highest then lookup result
    By Stalker in forum Excel Help
    Replies: 4
    Last Post: 04-02-2013, 02:04 PM
  2. finding the number of occurrence
    By zzzqinzzz in forum Excel Help
    Replies: 2
    Last Post: 12-13-2012, 10:24 AM
  3. Replies: 5
    Last Post: 09-26-2012, 10:17 AM
  4. Finding Credit and Debit Data and Creating Pivot
    By Prabhu in forum Excel Help
    Replies: 10
    Last Post: 01-29-2012, 11:03 PM
  5. Finding Last Used Row or Column In Excel Sheet
    By Rasm in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 04-14-2011, 03:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •