Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
find maximum
You want the LARGE( ) function, where you can specify the nth largest
value - described quite well in Excel Help. Pete |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I find value of "x" (col A) when I know "f(x)" (B)? | Excel Worksheet Functions | |||
How do I find value of "x" (col A) when I know "f(x)" (B)? | Excel Worksheet Functions | |||
Find largest alphanumeric value matching alpha criteria in databas | Excel Worksheet Functions | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
Upgraded to office 2003 now cannot find personal.xls | Excel Discussion (Misc queries) |