Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 27th 04, 10:19 PM
Peter B
 
Posts: n/a
Default Use MATCH to find position of max in 2D range?

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

  #2   Report Post  
Old October 27th 04, 10:25 PM
Frank Kabel
 
Posts: n/a
Default

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)))

--
Regards
Frank Kabel
Frankfurt, Germany

"Peter B" schrieb im Newsbeitrag
...
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


  #3   Report Post  
Old October 28th 04, 12:25 AM
Alan Beban
 
Posts: n/a
Default

Peter B wrote:

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
  #4   Report Post  
Old October 28th 04, 03:37 PM
Peter B
 
Posts: n/a
Default

Alan,

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

ptb

"Alan Beban" wrote:

Peter B wrote:

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

  #5   Report Post  
Old October 28th 04, 05:23 PM
Peter B
 
Posts: n/a
Default

Frank,

Amazing... it works! I haven't quite figured out why yet, but I'll take it
on faith until my brain kicks in.

Thank you so much.

ptb

"Frank Kabel" wrote:

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)))

--
Regards
Frank Kabel
Frankfurt, Germany

"Peter B" schrieb im Newsbeitrag
...
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





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
ClearContents method on a passed range bryan New Users to Excel 2 January 19th 05 08:49 AM
Defined range difficulty Pat Excel Discussion (Misc queries) 7 January 16th 05 09:52 PM
Excel has a "Find Next" command but no "Find Previous" command. Michael Fitzpatrick Excel Discussion (Misc queries) 2 January 10th 05 11:45 PM
find numbers in a range that add to a specific value Brett Excel Discussion (Misc queries) 1 December 20th 04 01:55 PM
named range refers to: in a chart Spencer Hutton Excel Discussion (Misc queries) 1 December 14th 04 10:15 PM


All times are GMT +1. The time now is 09:42 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017