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 |