Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Frank Drost
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
B. R.Ramachandran
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF/AND/OR/DATEIF Issue...sorry...long post... EDSTAFF Excel Worksheet Functions 1 November 10th 05 01:28 AM
Find a "date" in a column of dates in Excel 2000 JR Hester Excel Worksheet Functions 3 November 1st 05 10:17 PM
creating a bar graph Johnfli Excel Discussion (Misc queries) 0 October 26th 05 08:16 PM
Lookup Table Dilemma Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM
I need to find the Average from Column A - but Reference Column B BAM718 Excel Worksheet Functions 2 March 15th 05 03:42 PM


All times are GMT +1. The time now is 07:20 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"