find maximum
 

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

find maximum
 

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


"Frank Drost" wrote:

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

find maximum
 

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

Pete

find maximum
 

=LARGE(A1:I1,2)

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

Vaya con Dios,
Chuck, CABGx3


"Frank Drost" wrote in message
...
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

find maximum
 

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
"Frank Drost" wrote:

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

find maximum
 

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.



"Ron Coderre" wrote:

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


"Frank Drost" wrote:

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

find maximum
 

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


"Frank Drost" wrote in message
...
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.



"Ron Coderre" wrote:

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


"Frank Drost" wrote:

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

find maximum
 

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


"Frank Drost" wrote:

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.



"Ron Coderre" wrote:

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


"Frank Drost" wrote:

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