+ Reply to Thread
Results 1 to 3 of 3

To localize greatest value in long rows

  1. #1
    Registered User
    Join Date
    05-03-2006
    Location
    Mellerud, Sweden
    MS-Off Ver
    2016
    Posts
    70

    To localize greatest value in long rows

    Hello

    I have some dizzyingly long rows, wherein each cell has its "Bigger than.."-results of their respective columns above.
    In order to localize the greatest (and second biggest, and third, etc) value of each row at a glance, I've decided to spend an additional row underneath each of these rows, in which only the cell under the greatest value will manifest itself (preferably with the value), while the rest of them stay blank.
    Well, not quite: the problem arises when I want a formula to perform likewise, when also regarding the second largest value, and the third largest, and so on - in the same row.
    I solved the first problem, in a combination of suggestions from both the Help-program and some of you tipsters here.
    =IF(CV92=3453;CV92*1;"")
    The number 3453 is the f9(frozen) result of a "Greatest value" formula, ranging from, say, CN92 to EV92.
    This could surely be replaced by a "Second largest"-formula, and this one in its turn by a "third largest" and so on.
    The problem with this solution, is that the latest variation would erase the results of the former formula (the greater value), if copied along in one move.
    The ideal formula would not only present the greater, but also the succeeding values of request as they appear along the row.
    That would save a lot of time, and quite a little space in my worksheets.

    Any suggestions?
    Thanks in advance.
    bcb

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Would you not use =Max(A$1:A1001) for column A, =Max(B$1:B1001) for column B etc to get the largest value, then =Large(A1002:CV1002,2) for the 2nd largest of those Max values, then =Large(A1002:CV1002,3) for the 3rd largest etc?

    To display as you suggested you could specify (formula in A1003)
    =IF(A1002=Large($A1002:$CV1002,1),"Largest Value",if(A1002=Large($A1002:$cv1002,2),"Second Largest",if(A1002=Large($A1002:$CV1002,3),"Third Largest","")))
    and formula-fill that across the columns etc.

    Does this help?



    Quote Originally Posted by BCB
    Hello

    I have some dizzyingly long rows, wherein each cell has its "Bigger than.."-results of their respective columns above.
    In order to localize the greatest (and second biggest, and third, etc) value of each row at a glance, I've decided to spend an additional row underneath each of these rows, in which only the cell under the greatest value will manifest itself (preferably with the value), while the rest of them stay blank.
    Well, not quite: the problem arises when I want a formula to perform likewise, when also regarding the second largest value, and the third largest, and so on - in the same row.
    I solved the first problem, in a combination of suggestions from both the Help-program and some of you tipsters here.
    =IF(CV92=3453;CV92*1;"")
    The number 3453 is the f9(frozen) result of a "Greatest value" formula, ranging from, say, CN92 to EV92.
    This could surely be replaced by a "Second largest"-formula, and this one in its turn by a "third largest" and so on.
    The problem with this solution, is that the latest variation would erase the results of the former formula (the greater value), if copied along in one move.
    The ideal formula would not only present the greater, but also the succeeding values of request as they appear along the row.
    That would save a lot of time, and quite a little space in my worksheets.

    Any suggestions?
    Thanks in advance.
    bcb

  3. #3
    Registered User
    Join Date
    05-03-2006
    Location
    Mellerud, Sweden
    MS-Off Ver
    2016
    Posts
    70
    Thanks a lot
    It looks like just the kind of thing I'll be needing.
    Thanks again
    bcb

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1