Just a formula

[shadow12345]

Hi,

I have the below data and what im trying to get to is the average price for that month.
Just one value for each Month. (I cant make the months come up the table )


<table border = "1" cellspacing = "0" bordercolor="#999999">
<tr><td bgcolor="#C0C0C0"> </td>
<td align="center" bgcolor="#C0C0C0"><b>F</b></td><td align="center" bgcolor="#C0C0C0"><b>G</b></td><td align="center" bgcolor="#C0C0C0"><b>H</b></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>1</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Contract </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Price </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">Volume </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>2</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2492 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">50 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>3</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40787 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2525 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">50 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>4</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2491 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">3 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>5</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2490 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">7 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>6</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40787 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2523 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">200 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>7</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2490 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">1 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>8</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40848 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2544 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">250 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>9</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40664 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2479 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">20 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>10</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2490 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">6 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>11</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2490 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">1 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>12</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2490 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>13</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2490 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">12 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>14</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2489 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">200 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>15</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2489 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">100 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>16</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40787 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2523 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">100 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>17</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2489 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">94 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>18</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40787 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2523 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">94 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>19</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2489 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">50 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>20</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40787 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2523 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">50 </font></td></tr>
<tr><td align = "center" bgcolor="#C0C0C0"><b>21</b></td><td rowspan="1" colspan="1" width="225" height="30" align = "left" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">40725 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">2489 </font></td><td rowspan="1" colspan="1" width="192" height="30" align = "right" valign = "bottom" bgcolor ="#FFFFFF"><font color="#000000">17 </font></td></tr>
</table>

 

[Andrew Poulsom]

Isn't it?

=SUMPRODUCT(B2:B21,C2:C21)/SUM(C2:C21)

 

[njimack]

Which column holds the date of each contract?
And do presumably you want the weighted average price?

 

[sanrv1f]

may be one of these

Excel Workbook
	A	B	C
1	Contract	Price	Volume
2	Jul-11	2492	50
3	Sep-11	2525	50
4	Jul-11	2491	3
5	Jul-11	2490	7
6	Sep-11	2523	200
7	Jul-11	2490	1
8	Nov-11	2544	250
9	May-11	2479	20
10	Jul-11	2490	6
11	Jul-11	2490	1
12
13
14
15	Jul-11	2490.5	2491.5
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C15	=SUMPRODUCT(--(A2:A11=A15),B2:B11,C2:C11)/SUMPRODUCT(--(A2:A11=A15),C2:C11)

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[shadow12345]

Yes i do want to make it a weighted average... does sumproduct do that? I don't know how to use that one.