Help with sums

[HillBilly]

I have three columns and want an average price for the week when the week changes.
this is going to update daily so a VBA programme would be best but i will accept anything!!!

Price Week Ave. Price
1.50 18
2.50 18 x(for week 18)
2 19
3.00 19
4.50 19
1.75 19 x(for week 19)


Cheers

 

[Asala42]

G'day,

How about a formula solution? (For example's sake I'll assume that your dealing with data starting in row 2 thru 100, columns A,B, and C). This method assumes your week's column is sorted.

in C2: =IF(COUNTIF($B$2:$B$100,B2)>0,"",(SUMPRODUCT(($B$2:$B$100=B2)*($A$2:$A$100))/COUNTIF($B$2:$B$100,B2)))

in C3 (very similar): =IF(B3=B4,"",SUMPRODUCT(($B$2:$B$100=B2)*($A$2:$A$100))/COUNTIF($B$2:$B$100,B2)))

Hope that helps,
Adam

 

[Aladin Akyurek]

On 2002-05-01 06:16, HillBilly wrote:
I have three columns and want an average price for the week when the week changes.
this is going to update daily so a VBA programme would be best but i will accept anything!!!

Price Week Ave. Price
1.50 18
2.50 18 x(for week 18)
2 19
3.00 19
4.50 19
1.75 19 x(for week 19)
Cheers

Assume A1:B7 houses the sample data with labels in row 1.

In C2 enter and copy down:

=IF(B2<>B3,AVERAGE(OFFSET($A$2,MATCH(B2,B:B,0)-2,0,COUNTIF(B:B,B2),1)),"")

Note. This formula doesn't require modification in face of growing data range. it requires however that B is sorted in ascending order.

Aladin