I have the table below. The issue here is that I have about 40 different name codes in this data download. In Example 1 I need the following information
1) Weighted average of rate (Sumproduct of ABCDE Rate and Units)/Sum of Units (ABCDE)........0.431
2) Weighted Average of Total discount of ABCDE ( Sumproduct of ABCDE total disc and Units)/Sum of Units (ABCDE)..........0.642
3) (Weighted Average of total discount - Weighted Average of Rate ) X sum of Units (ABCDE)
The Issue here is that I have a big table and I want the formulas to reference ABCDE and selectively do the calcs from the table (kinda like vlookup and Index match). I want to type ABCDE in a cell and get the three calculations out. I don't know how to ask excel to reference the 5 letters (ABCDE) and reproduce the calculations. Any help is most appreciated. Thanks. The table is reproduced below; I also want these same calcs for the total database. (both ABCDE and EFGHI in this case). Thanks.
1) Weighted average of rate (Sumproduct of ABCDE Rate and Units)/Sum of Units (ABCDE)........0.431
2) Weighted Average of Total discount of ABCDE ( Sumproduct of ABCDE total disc and Units)/Sum of Units (ABCDE)..........0.642
3) (Weighted Average of total discount - Weighted Average of Rate ) X sum of Units (ABCDE)
The Issue here is that I have a big table and I want the formulas to reference ABCDE and selectively do the calcs from the table (kinda like vlookup and Index match). I want to type ABCDE in a cell and get the three calculations out. I don't know how to ask excel to reference the 5 letters (ABCDE) and reproduce the calculations. Any help is most appreciated. Thanks. The table is reproduced below; I also want these same calcs for the total database. (both ABCDE and EFGHI in this case). Thanks.
Name Code | Rate | Units | DISC 1 | DISC2 | DISC 3 | Total Discount |
ABCDE 1 | 0.52 | 5,487 | $ 0.40 | $ 0.10 | $ 0.14 | 0.64 |
ABCDE 2 | 0.47 | 6,312 | $ 0.42 | $ 0.10 | $ 0.14 | 0.66 |
ABCDE 3 | 0.38 | 9,147 | $ 0.43 | $ 0.10 | $ 0.14 | 0.67 |
ABCDE 4 | 0.36 | 2,568 | $ 0.36 | $ 0.10 | $ 0.14 | 0.60 |
ABCDE 5 | 0.42 | 6,589 | $ 0.38 | $ 0.10 | $ 0.14 | 0.62 |
EFGHI 1 | 0.50 | 6,583 | $ 0.32 | $ 0.20 | $ 0.14 | 0.66 |
EFGHI 2 | 0.23 | 7,893 | $ 0.28 | $ 0.20 | $ 0.14 | 0.62 |
EFGHI 3 | 0.19 | 5,489 | $ 0.35 | $ 0.20 | $ 0.14 | 0.69 |
EFGHI 4 | 0.38 | 1,258 | $ 0.38 | $ 0.20 | $ 0.14 | 0.72 |
EFGHI 5 | 0.42 | 9,657 | $ 0.40 | $ 0.20 | $ 0.14 | 0.74 |
EFGHI 6 | 0.28 | 1,237 | $ 0.50 | $ 0.20 | $ 0.14 | 0.84 |
Example 1 | ABCDE | |||||
Weighted Avg Rate | 0.431 | |||||
Weighted Avg Total Discount | 0.642 | |||||
Loss/Gain | $ 6,336.10 | |||||
Example 2 | EFGHI | |||||
Weighted Avg Rate | 0.343 | |||||
Weighted Avg Total Discount | 0.685 | |||||
Loss/Gain | $ 10,971.11 | |||||
Combined | ABCDE & EFGHI Combined | |||||
Weighted Avg Rate | 0.386 | |||||
Weighted Avg Total Discount | 0.664 | |||||
Loss/Gain | $ 17,307.21 |