Use MATCH to find position of max in 2D range?

[Peter B]

Here is the setup:

I have a 2D range with the 1/2 hour of the day (0:30 to 24:00) across the
top and the day of the month (say 09/01/04 to 09/30/04). The range is filled
with data that is taken each 1/2 hour for the whole month. I am trying to
find the DATE and TIME of the maximum value in that range. Call the rate
JanData.

I "thought" I could use MATCH in combination with MAX to return a reference
(row and column) to the correct date and time, but when I do so.

Here is my formula: =MATCH(MAX(JanData),JanData,0)

However I get #N/A.

Can MATCH be used in a 2-dimensional situation to return both row and
column? I don't see an info on this in all the literature.

Thanks

 

[Frank Kabel]

Hi
try (all array formulas entered with CTRL+SHIFT+ENTER)
row:
=MIN(IF(B2:X100=MAX(B2:X100),ROW(B2:X100)))

columns:
=MIN(IF(B2:X100=MAX(B2:X100),COLUMN(B2:X100)))

 

[Alan Beban]

Here is the setup:

I have a 2D range with the 1/2 hour of the day (0:30 to 24:00) across the
top and the day of the month (say 09/01/04 to 09/30/04). The range is filled
with data that is taken each 1/2 hour for the whole month. I am trying to
find the DATE and TIME of the maximum value in that range. Call the rate
JanData.

I "thought" I could use MATCH in combination with MAX to return a reference
(row and column) to the correct date and time, but when I do so.

Here is my formula: =MATCH(MAX(JanData),JanData,0)

However I get #N/A.

Can MATCH be used in a 2-dimensional situation to return both row and
column? I don't see an info on this in all the literature.

Thanks

Do you want the output in one cell or two?

Alan Beban

 

[Peter B]

Alan,

1 would be great, but I'll take 2!

ptb

Do you want the output in one cell or two?

Alan Beban