date range help

[currybai]

I have the following table

<table border="0" cellpadding="0" cellspacing="0" width="874"><col style="width: 140pt;" width="187" span="4"> <col style="width: 95pt;" width="126"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 140pt;" width="187" height="20">Date</td> <td style="width: 140pt;" width="187">Volume</td> <td style="width: 140pt;" width="187">Sales</td> <td style="width: 140pt;" width="187">Ratio</td> <td style="width: 95pt;" width="126">Index</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">7/1/2009 0:00</td> <td align="right">522424242</td> <td align="right">20312</td> <td class="xl66" align="right">0.0039%</td> <td align="right">95.18473528</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">7/2/2009 0:00</td> <td align="right">222424242</td> <td align="right">17236</td> <td class="xl66" align="right">0.0077%</td> <td align="right">189.7109095</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">7/3/2009 0:00</td> <td align="right">422425444</td> <td align="right">19767</td> <td class="xl66" align="right">0.0047%</td> <td align="right">114.5588467</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">7/4/2009 0:00</td> <td align="right">422424242</td> <td align="right">9808</td> <td class="xl66" align="right">0.0023%</td> <td align="right">56.84202791</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">7/5/2009 0:00</td> <td align="right">654897864</td> <td align="right">11691</td> <td class="xl66" align="right">0.0018%</td> <td align="right">43.70348063</td> </tr> </tbody></table>

So the dates go down daily till today. The last formula I have as index is, ratio of day/average of ratio of month. So for 7/1 its (d2/average($d2$:$d6$))*100


Is there a way I can change the range when the month changes? Like when it goes to 8/1-8/31, it will automatically take the average of that month and so forth?

 

[njimack]

Excel Workbook
	A	B	C	D	E
1	Date	Volume	Sales	Ratio	Index
2	01/07/2009 00:00	* * * * 522,424,242	* * 20,312	0.00%	95.58824
3	02/07/2009 00:00	* * * * 222,424,242	* * 17,236	0.01%	188.7255
4	03/07/2009 00:00	* * * * 422,425,444	* * 19,767	0.00%	115.1961
5	04/07/2009 00:00	* * * * 422,424,242	* * * *9,808	0.00%	56.37255
6	05/07/2009 00:00	* * * * 654,897,864	* * 11,691	0.00%	44.11765
Sheet1

 

[currybai]

Excel Workbook
	A	B	C	D	E
1	Date	Volume	Sales	Ratio	Index
2	01/07/2009 00:00	* * * * 522,424,242	* * 20,312	0.00%	95.58824
3	02/07/2009 00:00	* * * * 222,424,242	* * 17,236	0.01%	188.7255
4	03/07/2009 00:00	* * * * 422,425,444	* * 19,767	0.00%	115.1961
5	04/07/2009 00:00	* * * * 422,424,242	* * * *9,808	0.00%	56.37255
6	05/07/2009 00:00	* * * * 654,897,864	* * 11,691	0.00%	44.11765
Sheet1

Wow thank you!!

 

[njimack]

You're welcome.