Results 1 to 5 of 5

20051121, 22:33 #1
 Join Date
 May 2002
 Posts
 412
 Thanks
 1
 Thanked 0 Times in 0 Posts
array to calculate maximum and date of maximum (2003)
Edited by HansV to present data in table format
I have data in the following format
<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>1</td><td>Monitor site</td><td>date</td><td>Water Level</td><td align=center>2</td><td>WECYC815s</td><td align=right>10/4/2004</td><td align=right>69.08</td><td align=center>3</td><td>WECYC815s</td><td align=right>10/18/2004</td><td align=right>66.65</td><td align=center>4</td><td>WECYC815s</td><td align=right>11/5/2004</td><td align=right>65.58</td></table>
And need to pull out the maximum water level for the following table.
<table border=1><td></td><td align=center>H</td><td align=center>I</td><td align=center>J</td><td align=center>K</td><td align=center>1</td><td>Lookup value</td><td>Monitor Well</td><td>Maximum Water Level (ft. NGVD)</td><td>Date</td><td align=center>2</td><td> WECYC815s</td><td>815s (NGS)</td><td align=right>88.74</td><td align=right>8/1/2005</td><td align=center>3</td><td> WECYC816s</td><td>816s (SR52S)</td><td align=right>77.14</td><td align=right>9/9/2004</td><td align=center>4</td><td> WECYC821s</td><td>821S</td><td align=right>75.68</td><td align=right>9/9/2004</td></table>
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

20051121, 22:44 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: array to calculate maximum and date of maximum (2003)
In D2, enter the formula <code>=A2&""&C2</code> and fill down to D7821.
Enter the following formula in K2:
<code>
=INDEX($B$2:$7821,MATCH(H2&""&J2,$D$2:$D$7821,0))
</code>
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.

20051122, 07:51 #3
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,124
 Thanks
 2
 Thanked 439 Times in 362 Posts
Re: array to calculate maximum and date of maximum (2003)
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 (<CtrlShiftEnter>). Format the result as a date and copy down to K4.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20051122, 13:46 #4
 Join Date
 May 2002
 Posts
 412
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: array to calculate maximum and date of maximum (2003)
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).

20051122, 21:51 #5
 Join Date
 Jan 2004
 Location
 Birmingham, West Midlands, United Kingdom
 Posts
 219
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: array to calculate maximum and date of maximum
>>The breadth of knowledge in this forum is amazing...
Not to mention the depth, too!
I second your remarks.