Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find which column has the maximum value
I have to check each row for the maximum value in that row. But instead of
writing the maximum value of that row, I have to write the column number of that maximum value. The very first row of my dataset goes from 1 to 100, indicating 100 columns, and is there only to number the columns. So, the column number has to be picked from that row. For instance, in row 20, the maximum value is 10, and it is at column 56. How can I as output of a formula (or conditional formatting) get as answer 56? ta |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
find which column has the maximum value
Try this
=MATCH(MAX(20:20),20:20,0) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Frank Drost" wrote in message ... I have to check each row for the maximum value in that row. But instead of writing the maximum value of that row, I have to write the column number of that maximum value. The very first row of my dataset goes from 1 to 100, indicating 100 columns, and is there only to number the columns. So, the column number has to be picked from that row. For instance, in row 20, the maximum value is 10, and it is at column 56. How can I as output of a formula (or conditional formatting) get as answer 56? ta |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
find which column has the maximum value
Hi,
If your data start in Column A (so the column range is A:CV for your 100 columns), use the following formula in may be CW (i.e., column 101) and autofill down the rows. =MATCH(MAX(A2:CV2),A2:CV2,0) If your data start in some other column, say Column C, (so the range is C:CX), use the following modification. =MATCH(MAX(C2:CX2),C2:CX2,0)+COLUMN(C2)-1 Regards, B. R. Ramachandran "Frank Drost" wrote: I have to check each row for the maximum value in that row. But instead of writing the maximum value of that row, I have to write the column number of that maximum value. The very first row of my dataset goes from 1 to 100, indicating 100 columns, and is there only to number the columns. So, the column number has to be picked from that row. For instance, in row 20, the maximum value is 10, and it is at column 56. How can I as output of a formula (or conditional formatting) get as answer 56? ta |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF/AND/OR/DATEIF Issue...sorry...long post... | Excel Worksheet Functions | |||
Find a "date" in a column of dates in Excel 2000 | Excel Worksheet Functions | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
I need to find the Average from Column A - but Reference Column B | Excel Worksheet Functions |