Results 1 to 2 of 2
Thread: average a range (2002 xp)

20050725, 16:34 #1
 Join Date
 May 2002
 Posts
 411
 Thanks
 1
 Thanked 0 Times in 0 Posts
average a range (2002 xp)
Edited by HansV to present data as table
I have data arranged in columns by date, water level, water level as shown below. The dates are in ascending order, but are not consecutive. I need to be able to enter two dates; e.g. 9/20/78 and 10/31/78 and have the formula return the average value for the inclusive range; which in this example would be 54.65 and 62.83. I think I want DAVERAGE?
Thanks.
<table border=1><td align=right>Date</td><td align=right>826d</td><td align=right>826s</td><td align=right>8/15/78</td><td align=right>59.79</td><td align=right>65.74</td><td align=right>8/29/78</td><td align=right>57.34</td><td align=right>63.19</td><td align=right>9/20/78</td><td align=right>60.29</td><td align=right>62.66</td><td align=right>9/26/78</td><td align=right>56.35</td><td align=right>64.00</td><td align=right>10/3/78</td><td align=right>56.35</td><td align=right>63.31</td><td align=right>10/17/78</td><td align=right>51.23</td><td align=right>62.11</td><td align=right>10/31/78</td><td align=right>49.05</td><td align=right>62.06</td><td align=right>11/21/78</td><td align=right>47.19</td><td align=right>60.78</td><td align=right>11/28/78</td><td align=right>46.90</td><td align=right>60.60</td><td align=right>12/5/78</td><td align=right>46.87</td><td align=right>60.80</td><td align=right>12/12/78</td><td align=right>47.48</td><td align=right>61.34</td><td align=right>12/27/78</td><td align=right>46.40</td><td align=right>61.06</td><td align=right>1/9/79</td><td align=right>47.79</td><td align=right>63.14</td><td align=right>1/16/79</td><td align=right>48.90</td><td align=right>63.93</td><td align=right>1/23/79</td><td align=right>48.91</td><td align=right>63.58</td></table>

20050725, 16:52 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: average a range (2002 xp)
Say that your table is in A1:C16, with the field names in A1:C1 and the data in A2:C16
Enter the start date in cell F1, and the end date in cell F2.
The following array formulas (i.e. confirm with Ctrl+Shift+Enter instead of Enter) will calculate the average of the cells in volumn B whose dates are between the start and end dates:
=AVERAGE(IF((A2:A16>=F1)*(A2:A16<=F2),B2:B16))
=AVERAGE(IF((A2:A16>=F1)*(A2:A16<=F2),C2:C16))