find maximum

[Guest]

In a table, I need to find for each row the second largest value. Undoubtedly
this can be done with conditional formatting, but I don't know how. Something
like find maximum for a range of cells as long no cell is the maximum of that
row, right? For instance, the following row has:
0 2 4 5 3 7 8 3 2 6
I want to have as answer nr 7 (8 is maximum, 7 is next highest)

does anyone know how to do that?

Thanks

 

[Guest]

Try this:
For your data in cells A1:J1

K1: =LARGE(A1:J1,2)

That returns the second largest value in the referenced range.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Pete]

You want the LARGE( ) function, where you can specify the nth largest
value - described quite well in Excel Help.

Pete

 

[CLR]

=LARGE(A1:I1,2)

Will return the second largest value in the range.....

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

if 0 is located in A7 then:
click in A7
go to:
format--->conditional formatting-->cell formula is-->=RANK(A7,$A$7:$J$7,0)=2

click on formatt-->pattern -->change to highlight different color-->OK

Copy and special paste the formatting to all other cells (B7 to J7), 7 is
highlighted now.

Hope this help.

Nikki

 

[Guest]

Thanks Ron. That command works.
However, I actually made a small, but crucial mistake in my query. I am not
actually after the second largest value, but its location in its row. For
instance, as in my example, the number 7 is the second largest value, and its
location in that row is nr 6. And then to make the complication complete, I
then want as answer the value of cell 6 in row X (x is a variable here, but
will often be the first row in my table). To do that I think I need to do
conditional formatting. It seems I have to use LARGE, but what else? Can you
help me with this?

Thanks.

 

[CLR]

Format the first cell of your range as follows.....

Format > conditionalFormat > CellValueIs > EqualTo > =LARGE($A$1:$I$1,2),
and set format as desired (adjust range to your needs)

Then copy and paste that format using the FormatPainter to the other cells
in your range..........ALL cells equaling the second highest value in the
range will appear with the Conditional Format.....

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Based on this post, it appears that your data might be in columns, so I'll
work with that.

For this example:
Your data is in cells A1:A10
The secondary data you want is in cells C1:C10

E1: 3 (this is the column reference to pull data from)

F1: =INDEX(A1:C10,MATCH(LARGE(A1:A10,2),A1:A10,0),E1)

In this case, the function returns the value of the cell in A1:C10 that is 6
rows down from the top and 3 cells from the left in that range.

One thing to be aware of:
If there was more than one 7 in your range....the LARGE function would call
one of them 2nd largest and the next would be 3rd largest.


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro