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
You want the LARGE( ) function, where you can specify the nth largest
value - described quite well in Excel Help.
Pete
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
=LARGE(A1:I1,2)
Will return the second largest value in the range.....
Vaya con Dios,
Chuck, CABGx3
"Frank Drost" <[email protected]> wrote in message
news:[email protected]...
> 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
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
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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks