Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate weighted average effective interest rate | Excel Worksheet Functions | |||
Calculating Weighted Average | Excel Worksheet Functions | |||
Calculating a weighted average in a pivot table | Excel Discussion (Misc queries) | |||
calculating a weighted average using formula | Excel Worksheet Functions | |||
calculating a weighted average uisng formula | Excel Worksheet Functions |