Calculating a Grouped Weighted Average Rate

[sylink]

I need to calculate the weighted-av-rate using the table below. The
groupings is the TX. The wieghted-av-rate, WVR for 1st day in a TX
group remains same as the rate. E.g in TX A, weighted-av-rate = 1. for
day 2 in TX A, the WVR = ((30*1)+(40*5))/(30+40) and so on. The WVR for
TX B, day 4 is same as the rate,i.e 9. day 5 is obtained using the same
formulae in TX A

pls generate a macro to accomplish this.



DAY TX BALANCE RATE WEIGHTED-AV-RATE
1 A 30 1
2 A 40 5
3 A 10 6
4 B 20 9
5 B 50 5
6 B 30 3
7 C 20 3

thanks.

 

[Guest]

I can do it with worksheet formulas, if you can allow one additional
calculated column. Will assume your columns (DAY, TX, BALANCE, RATE) are
A-D. I inserted a column for E and entered this formula in E2 (and copied
down):
=D2*C2
Then for the weighted avg rate, I use this formula in F2 and copied down:
=SUMIF(B:B,B2,E:E)/SUMIF(B:B,B2,C:C)

This puts the weighted avg on each line, if you want to only have it on the
last row for any particular value of TX, I would qualify it as follows:
=IF(B2=B3,"",SUMIF(B:B,B2,E:E)/SUMIF(B:B,B2,C:C))