Thanks:  0
Likes:  0

1. 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

2. 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,

3. 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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•