Calculating a Grouped Weighted Average Rate
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. |
Calculating a Grouped Weighted Average Rate
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)) "sylink" wrote: 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. |
All times are GMT +1. The time now is 01:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com