Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformulating question regarding maximum value
I had an hour ago put a question up named "find maximum", but I had not
formulated my query fully, hence this query. In each row, I want to find the second highest value in that row, and then as output give the value of that cell in the first row. For example: 1 2 6 8 6 5 4 3 2 4 5 6 3 2 3 8 6 4 5 3 7 2 3 1 The second largest value in the second row is nr 6. It is in the fourht cell of that row. The fourth cell of the first row is the value 8. This should be the output for the second row. The second largest value in the third row is the number 6. It is in the first cell of that row. The first cell of the first row has the value 1. This should be the output for the third row. I assume that such a thing needs to be set up with conditional formatting. Does anyone have an idea how to do this? Regards, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformulating question regarding maximum value
Hi,
Lets say your range is between cells A1 to H3, Use =INDEX($A$1:$H$3,1,MATCH(LARGE($A$2:$H$2,2),$A$2:$ H$2,0)) to get the value of second largest value in Row 2 and to return the corresponding vlaue in Row 1 use =INDEX($A$1:$H$3,1,MATCH(LARGE($A$3:$H$3,2),$A$3:$ H$3,0)) to get the value of second largest value in Row 3 and to return the corresponding value in Row 1 Regards Govind. Frank Drost wrote: I had an hour ago put a question up named "find maximum", but I had not formulated my query fully, hence this query. In each row, I want to find the second highest value in that row, and then as output give the value of that cell in the first row. For example: 1 2 6 8 6 5 4 3 2 4 5 6 3 2 3 8 6 4 5 3 7 2 3 1 The second largest value in the second row is nr 6. It is in the fourht cell of that row. The fourth cell of the first row is the value 8. This should be the output for the second row. The second largest value in the third row is the number 6. It is in the first cell of that row. The first cell of the first row has the value 1. This should be the output for the third row. I assume that such a thing needs to be set up with conditional formatting. Does anyone have an idea how to do this? Regards, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
reformulating question regarding maximum value
If your numbers run from Column A to H,
Try this formula in I2: =INDEX($A$1:$H$1,MATCH(LARGE($A2:$H2,2),$A2:$H2,0) ) And copy down as needed. In case of ties, this will return the location of the *first* second largest number. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Frank Drost" wrote in message ... I had an hour ago put a question up named "find maximum", but I had not formulated my query fully, hence this query. In each row, I want to find the second highest value in that row, and then as output give the value of that cell in the first row. For example: 1 2 6 8 6 5 4 3 2 4 5 6 3 2 3 8 6 4 5 3 7 2 3 1 The second largest value in the second row is nr 6. It is in the fourht cell of that row. The fourth cell of the first row is the value 8. This should be the output for the second row. The second largest value in the third row is the number 6. It is in the first cell of that row. The first cell of the first row has the value 1. This should be the output for the third row. I assume that such a thing needs to be set up with conditional formatting. Does anyone have an idea how to do this? Regards, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table question | Excel Discussion (Misc queries) | |||
find which column has the maximum value | Excel Discussion (Misc queries) | |||
Tricky 'Find Maximum' problem seeks formula | Excel Worksheet Functions | |||
Finding the maximum of a subset of values on a different sheet | Excel Discussion (Misc queries) | |||
How do I find and replace a question mark in Excel? | Excel Discussion (Misc queries) |