Need Formula Help to Selectively Calculate Weighted Average

[Grepees]

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.

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

 

[DanteAmor]

I don't know how you calculate the Loss/Gain concept, if you could explain how you get to that result I can gladly help you.
While checking if the following helps you:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Name Code	Rate	Units	DISC 1	DISC2	DISC 3	Total Discount
2	ABCDE	0.52	5487	0.4	0.1	0.14	0.64			ABCDE
3	ABCDE	0.47	6312	0.42	0.1	0.14	0.66			Weighted Avg Rate	0.430
4	ABCDE	0.38	9147	0.43	0.1	0.14	0.67			Weighted Avg Total Discount	0.638
5	ABCDE	0.36	2568	0.36	0.1	0.14	0.6			Loss/Gain	???
6	ABCDE	0.42	6589	0.38	0.1	0.14	0.62
7	EFGHI	0.5	6583	0.32	0.2	0.14	0.66			EFGHI
8	EFGHI	0.23	7893	0.28	0.2	0.14	0.62			Weighted Avg Rate	0.333
9	EFGHI	0.19	5489	0.35	0.2	0.14	0.69			Weighted Avg Total Discount	0.712
10	EFGHI	0.38	1258	0.38	0.2	0.14	0.72			Loss/Gain	???
11	EFGHI	0.42	9657	0.4	0.2	0.14	0.74
12	EFGHI	0.28	1237	0.5	0.2	0.14	0.84			ABCDE & EFGHI Combined
13										Weighted Avg Rate	0.377
14										Weighted Avg Total Discount	0.678
15										Loss/Gain	???
sheet

Cell Formulas
Range	Formula
K3	K3	=AVERAGEIF($A$2:$A$12,$J$2&"*",$B$2:$B$12)
K4	K4	=AVERAGEIF($A$2:$A$12,$J$2&"*",$G$2:$G$12)
K8	K8	=AVERAGEIF($A$2:$A$12,$J$7&"*",$B$2:$B$12)
K9	K9	=AVERAGEIF($A$2:$A$12,$J$7&"*",$G$2:$G$12)
K13	K13	=AVERAGE($B$2:$B$12)
K14	K14	=AVERAGE($G$2:$G$12)

 

[Grepees]

I don't know how you calculate the Loss/Gain concept, if you could explain how you get to that result I can gladly help you.
While checking if the following helps you:

Thank you very much. I will take a look at the formulas and see if they work and get back to you. Here is the answer to your question of loss/gain concept.


The loss/Gain concept is (Weighted Avg Total Discount- Weighted Avg Rate)/sum of total units of ABCDE

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Name Code	Rate	Units	DISC 1	DISC2	DISC 3	Total Discount
2	ABCDE	0.52	5487	0.4	0.1	0.14	0.64			ABCDE
3	ABCDE	0.47	6312	0.42	0.1	0.14	0.66			Weighted Avg Rate	0.430
4	ABCDE	0.38	9147	0.43	0.1	0.14	0.67			Weighted Avg Total Discount	0.638
5	ABCDE	0.36	2568	0.36	0.1	0.14	0.6			Loss/Gain	???
6	ABCDE	0.42	6589	0.38	0.1	0.14	0.62
7	EFGHI	0.5	6583	0.32	0.2	0.14	0.66			EFGHI
8	EFGHI	0.23	7893	0.28	0.2	0.14	0.62			Weighted Avg Rate	0.333
9	EFGHI	0.19	5489	0.35	0.2	0.14	0.69			Weighted Avg Total Discount	0.712
10	EFGHI	0.38	1258	0.38	0.2	0.14	0.72			Loss/Gain	???
11	EFGHI	0.42	9657	0.4	0.2	0.14	0.74
12	EFGHI	0.28	1237	0.5	0.2	0.14	0.84			ABCDE & EFGHI Combined
13										Weighted Avg Rate	0.377
14										Weighted Avg Total Discount	0.678
15										Loss/Gain	???
sheet

Cell Formulas
Range	Formula
K3	K3	=AVERAGEIF($A$2:$A$12,$J$2&"*",$B$2:$B$12)
K4	K4	=AVERAGEIF($A$2:$A$12,$J$2&"*",$G$2:$G$12)
K8	K8	=AVERAGEIF($A$2:$A$12,$J$7&"*",$B$2:$B$12)
K9	K9	=AVERAGEIF($A$2:$A$12,$J$7&"*",$G$2:$G$12)
K13	K13	=AVERAGE($B$2:$B$12)
K14	K14	=AVERAGE($G$2:$G$12)

 

[DanteAmor]

I'm sorry, I didn't check your requirement well, try these formulas:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Name Code	Rate	Units	DISC 1	DISC2	DISC 3	Total Discount
2	ABCDE 1	0.52	5,487	0.4	0.1	0.14	0.64			ABCDE
3	ABCDE 2	0.47	6,312	0.42	0.1	0.14	0.66			Weighted Avg Rate	0.431
4	ABCDE 3	0.38	9,147	0.43	0.1	0.14	0.67			Weighted Avg Total Discount	0.646
5	ABCDE 4	0.36	2,568	0.36	0.1	0.14	0.6			Loss/Gain	6,444
6	ABCDE 5	0.42	6,589	0.38	0.1	0.14	0.62
7	EFGHI 1	0.5	6,583	0.32	0.2	0.14	0.66			EFGHI
8	EFGHI 2	0.23	7,893	0.28	0.2	0.14	0.62			Weighted Avg Rate	0.343
9	EFGHI 3	0.19	5,489	0.35	0.2	0.14	0.69			Weighted Avg Total Discount	0.689
10	EFGHI 4	0.38	1,258	0.38	0.2	0.14	0.72			Loss/Gain	11,087
11	EFGHI 5	0.42	9,657	0.4	0.2	0.14	0.74
12	EFGHI 6	0.28	1,237	0.5	0.2	0.14	0.84			ABCDE & EFGHI Combined
13										Weighted Avg Rate	0.386
14										Weighted Avg Total Discount	0.668
15										Loss/Gain	17,531
sheet

Cell Formulas
Range	Formula
K3	K3	=SUMPRODUCT((LEFT($A$2:$A$12,5)=$J$2)*($B$2:$B$12)*($C$2:$C$12))/SUMIF($A$2:$A$12,$J$2&"*",$C$2:$C$12)
K4	K4	=SUMPRODUCT((LEFT($A$2:$A$12,5)=$J$2)*($G$2:$G$12)*($C$2:$C$12))/SUMIF($A$2:$A$12,$J$2&"*",$C$2:$C$12)
K5	K5	=($K$4-$K$3)*SUMIF($A$2:$A$12,$J$2&"*",$C$2:$C$12)
K8	K8	=SUMPRODUCT((LEFT($A$2:$A$12,5)=$J$7)*($B$2:$B$12)*($C$2:$C$12))/SUMIF($A$2:$A$12,$J$7&"*",$C$2:$C$12)
K9	K9	=SUMPRODUCT((LEFT($A$2:$A$12,5)=$J$7)*($G$2:$G$12)*($C$2:$C$12))/SUMIF($A$2:$A$12,$J$7&"*",$C$2:$C$12)
K10	K10	=($K$9-$K$8)*SUMIF($A$2:$A$12,$J$7&"*",$C$2:$C$12)
K13	K13	=SUMPRODUCT(($B$2:$B$12)*($C$2:$C$12))/SUM($C$2:$C$12)
K14	K14	=SUMPRODUCT(($G$2:$G$12)*($C$2:$C$12))/SUM($C$2:$C$12)
K15	K15	=($K$14-$K$13)*SUM($C$2:$C$12)

 

[Robinish]

I'm sorry, I didn't check your requirement well, try these formulas:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Name Code	Rate	Units	DISC 1	DISC2	DISC 3	Total Discount
2	ABCDE 1	0.52	5,487	0.4	0.1	0.14	0.64			ABCDE
3	ABCDE 2	0.47	6,312	0.42	0.1	0.14	0.66			Weighted Avg Rate	0.431
4	ABCDE 3	0.38	9,147	0.43	0.1	0.14	0.67			Weighted Avg Total Discount	0.646
5	ABCDE 4	0.36	2,568	0.36	0.1	0.14	0.6			Loss/Gain	6,444
6	ABCDE 5	0.42	6,589	0.38	0.1	0.14	0.62
7	EFGHI 1	0.5	6,583	0.32	0.2	0.14	0.66			EFGHI
8	EFGHI 2	0.23	7,893	0.28	0.2	0.14	0.62			Weighted Avg Rate	0.343
9	EFGHI 3	0.19	5,489	0.35	0.2	0.14	0.69			Weighted Avg Total Discount	0.689
10	EFGHI 4	0.38	1,258	0.38	0.2	0.14	0.72			Loss/Gain	11,087
11	EFGHI 5	0.42	9,657	0.4	0.2	0.14	0.74
12	EFGHI 6	0.28	1,237	0.5	0.2	0.14	0.84			ABCDE & EFGHI Combined
13										Weighted Avg Rate	0.386
14										Weighted Avg Total Discount	0.668
15										Loss/Gain	17,531
sheet

Cell Formulas
Range	Formula
K3	K3	=SUMPRODUCT((LEFT($A$2:$A$12,5)=$J$2)*($B$2:$B$12)*($C$2:$C$12))/SUMIF($A$2:$A$12,$J$2&"*",$C$2:$C$12)
K4	K4	=SUMPRODUCT((LEFT($A$2:$A$12,5)=$J$2)*($G$2:$G$12)*($C$2:$C$12))/SUMIF($A$2:$A$12,$J$2&"*",$C$2:$C$12)
K5	K5	=($K$4-$K$3)*SUMIF($A$2:$A$12,$J$2&"*",$C$2:$C$12)
K8	K8	=SUMPRODUCT((LEFT($A$2:$A$12,5)=$J$7)*($B$2:$B$12)*($C$2:$C$12))/SUMIF($A$2:$A$12,$J$7&"*",$C$2:$C$12)
K9	K9	=SUMPRODUCT((LEFT($A$2:$A$12,5)=$J$7)*($G$2:$G$12)*($C$2:$C$12))/SUMIF($A$2:$A$12,$J$7&"*",$C$2:$C$12)
K10	K10	=($K$9-$K$8)*SUMIF($A$2:$A$12,$J$7&"*",$C$2:$C$12)
K13	K13	=SUMPRODUCT(($B$2:$B$12)*($C$2:$C$12))/SUM($C$2:$C$12)
K14	K14	=SUMPRODUCT(($G$2:$G$12)*($C$2:$C$12))/SUM($C$2:$C$12)
K15	K15	=($K$14-$K$13)*SUM($C$2:$C$12)

Can you refine the formula provided to apply a non-zero weighted average?

I am using the sample data set provided, but in the first column I am using only the two sets of letters, without the sequential numbers added. I’ve successfully converted your formula as follows:

=SUMPRODUCT((($A$2:$A$12)=$J2)*($B$2:$B$12)*($C$2:$C$12))/SUMIF($A$2:$A$12,$J2&"*",$C$2:$C$12)

In the example given, all of the data is populated. If let’s say the rate of 0.47 is missing in the second record, then the result of the formula above is 0.33289.

However the result I’m looking for instead using non-zero weighted average is 0.42121.

The way I achieve that currently is to add a new helper Units column with an IF statement to convert the Units to zero for any record where the Rate is blank. I can still do that but I would like to avoid creating the helper column.

I tried adding AND IsBlank to the first part of SUMPRODUCT, so that only rows where Name Code is ABCDE and Rate is not blank are included in the calculation, but this doesn’t work (I get zero):

=SUMPRODUCT(AND(ISBLANK($B$2:$B$12)=FALSE,(($A$2:$A$12)=$J2))*($B$2:$B$12)*($C$2:$C$12))/SUMIF($A$2:$A$12,$J2&"*",$C$2:$C$12)