array to calculate maximum and date of maximum (2003)

Topic

New Reply

Edited by HansV to present data in table format

I have data in the following format

	A	B	C
1	Monitor site	date	Water Level
2	WE-CYC-815s	10/4/2004	69.08
3	WE-CYC-815s	10/18/2004	66.65
4	WE-CYC-815s	11/5/2004	65.58

And need to pull out the maximum water level for the following table.

	H	I	J	K
1	Lookup value	Monitor Well	Maximum Water Level (ft. NGVD)	Date
2	WE-CYC-815s	815s (NGS)	88.74	8/1/2005
3	WE-CYC-816s	816s (SR52S)	77.14	9/9/2004
4	WE-CYC-821-s	821S	75.68	9/9/2004

I am using (for cell j3)

{=MAX(IF($A$2:$A$7821=H3,$C$2:$C$7821,””))}

To pull out the maximum value.

What I can

Reply | Quote

Replies

In D2, enter the formula =A2&"|"&C2 and fill down to D7821.
Enter the following formula in K2:

=INDEX($B$2:$7821,MATCH(H2&"|"&J2,$D$2:$D$7821,0))

and fill down to K7821.

Note: the character “|” is rather arbitrary, it can be replaced with another character that is unlikely to occur in columns A and C.

Reply | Quote

Thank you once again.

The breadth of knowledge in this forum is amazing (and a great resource for people like me who think that Excel should be able to do something, but can’t figure it out).

Reply | Quote

>>The breadth of knowledge in this forum is amazing…

Not to mention the depth, too!
I second your remarks.

Reply | Quote

Hi,

In K2 you could use:
=SUM(IF(($A$2:$A$7821=H2)*($C$2:$C$7821=J2),$B$2:$B$7821,))/SUM(IF(($A$2:$A$7821=H2)*($C$2:$C$7821=J2),1,))
entered as an array formula (). Format the result as a date and copy down to K4.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote