# To localize greatest value in long rows

1. ## 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  Register To Reply

2. 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? 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  Register To Reply

3. Thanks a lot
It looks like just the kind of thing I'll be needing.
Thanks again
bcb  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### 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