Need Formula Help to Selectively Calculate Weighted Average

Grepees

Board Regular
Joined
Apr 15, 2009
Messages
61
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 CodeRateUnitsDISC 1DISC2DISC 3Total Discount
ABCDE 10.525,487$ 0.40$ 0.10$ 0.140.64
ABCDE 20.476,312$ 0.42$ 0.10$ 0.140.66
ABCDE 30.389,147$ 0.43$ 0.10$ 0.140.67
ABCDE 40.362,568$ 0.36$ 0.10$ 0.140.60
ABCDE 50.426,589$ 0.38$ 0.10$ 0.140.62
EFGHI 10.506,583$ 0.32$ 0.20$ 0.140.66
EFGHI 20.237,893$ 0.28$ 0.20$ 0.140.62
EFGHI 30.195,489$ 0.35$ 0.20$ 0.140.69
EFGHI 40.381,258$ 0.38$ 0.20$ 0.140.72
EFGHI 50.429,657$ 0.40$ 0.20$ 0.140.74
EFGHI 60.281,237$ 0.50$ 0.20$ 0.140.84
Example 1ABCDE
Weighted Avg Rate 0.431
Weighted Avg Total Discount0.642
Loss/Gain$ 6,336.10
Example 2EFGHI
Weighted Avg Rate 0.343
Weighted Avg Total Discount0.685
Loss/Gain$ 10,971.11
CombinedABCDE & EFGHI Combined
Weighted Avg Rate0.386
Weighted Avg Total Discount0.664
Loss/Gain$ 17,307.21
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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
ABCDEFGHIJK
1Name CodeRateUnitsDISC 1DISC2DISC 3Total Discount
2ABCDE0.5254870.40.10.140.64ABCDE
3ABCDE0.4763120.420.10.140.66Weighted Avg Rate0.430
4ABCDE0.3891470.430.10.140.67Weighted Avg Total Discount0.638
5ABCDE0.3625680.360.10.140.6Loss/Gain???
6ABCDE0.4265890.380.10.140.62
7EFGHI0.565830.320.20.140.66EFGHI
8EFGHI0.2378930.280.20.140.62Weighted Avg Rate0.333
9EFGHI0.1954890.350.20.140.69Weighted Avg Total Discount0.712
10EFGHI0.3812580.380.20.140.72Loss/Gain???
11EFGHI0.4296570.40.20.140.74
12EFGHI0.2812370.50.20.140.84ABCDE & EFGHI Combined
13Weighted Avg Rate0.377
14Weighted Avg Total Discount0.678
15Loss/Gain???
sheet
Cell Formulas
RangeFormula
K3K3=AVERAGEIF($A$2:$A$12,$J$2&"*",$B$2:$B$12)
K4K4=AVERAGEIF($A$2:$A$12,$J$2&"*",$G$2:$G$12)
K8K8=AVERAGEIF($A$2:$A$12,$J$7&"*",$B$2:$B$12)
K9K9=AVERAGEIF($A$2:$A$12,$J$7&"*",$G$2:$G$12)
K13K13=AVERAGE($B$2:$B$12)
K14K14=AVERAGE($G$2:$G$12)
 

Grepees

Board Regular
Joined
Apr 15, 2009
Messages
61
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
ABCDEFGHIJK
1Name CodeRateUnitsDISC 1DISC2DISC 3Total Discount
2ABCDE0.5254870.40.10.140.64ABCDE
3ABCDE0.4763120.420.10.140.66Weighted Avg Rate0.430
4ABCDE0.3891470.430.10.140.67Weighted Avg Total Discount0.638
5ABCDE0.3625680.360.10.140.6Loss/Gain???
6ABCDE0.4265890.380.10.140.62
7EFGHI0.565830.320.20.140.66EFGHI
8EFGHI0.2378930.280.20.140.62Weighted Avg Rate0.333
9EFGHI0.1954890.350.20.140.69Weighted Avg Total Discount0.712
10EFGHI0.3812580.380.20.140.72Loss/Gain???
11EFGHI0.4296570.40.20.140.74
12EFGHI0.2812370.50.20.140.84ABCDE & EFGHI Combined
13Weighted Avg Rate0.377
14Weighted Avg Total Discount0.678
15Loss/Gain???
sheet
Cell Formulas
RangeFormula
K3K3=AVERAGEIF($A$2:$A$12,$J$2&"*",$B$2:$B$12)
K4K4=AVERAGEIF($A$2:$A$12,$J$2&"*",$G$2:$G$12)
K8K8=AVERAGEIF($A$2:$A$12,$J$7&"*",$B$2:$B$12)
K9K9=AVERAGEIF($A$2:$A$12,$J$7&"*",$G$2:$G$12)
K13K13=AVERAGE($B$2:$B$12)
K14K14=AVERAGE($G$2:$G$12)
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I'm sorry, I didn't check your requirement well, try these formulas:

Book1
ABCDEFGHIJK
1Name CodeRateUnitsDISC 1DISC2DISC 3Total Discount
2ABCDE 10.525,4870.40.10.140.64ABCDE
3ABCDE 20.476,3120.420.10.140.66Weighted Avg Rate0.431
4ABCDE 30.389,1470.430.10.140.67Weighted Avg Total Discount0.646
5ABCDE 40.362,5680.360.10.140.6Loss/Gain6,444
6ABCDE 50.426,5890.380.10.140.62
7EFGHI 10.56,5830.320.20.140.66EFGHI
8EFGHI 20.237,8930.280.20.140.62Weighted Avg Rate0.343
9EFGHI 30.195,4890.350.20.140.69Weighted Avg Total Discount0.689
10EFGHI 40.381,2580.380.20.140.72Loss/Gain11,087
11EFGHI 50.429,6570.40.20.140.74
12EFGHI 60.281,2370.50.20.140.84ABCDE & EFGHI Combined
13Weighted Avg Rate0.386
14Weighted Avg Total Discount0.668
15Loss/Gain17,531
sheet
Cell Formulas
RangeFormula
K3K3=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)
K4K4=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)
K5K5=($K$4-$K$3)*SUMIF($A$2:$A$12,$J$2&"*",$C$2:$C$12)
K8K8=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)
K9K9=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)
K10K10=($K$9-$K$8)*SUMIF($A$2:$A$12,$J$7&"*",$C$2:$C$12)
K13K13=SUMPRODUCT(($B$2:$B$12)*($C$2:$C$12))/SUM($C$2:$C$12)
K14K14=SUMPRODUCT(($G$2:$G$12)*($C$2:$C$12))/SUM($C$2:$C$12)
K15K15=($K$14-$K$13)*SUM($C$2:$C$12)
 

Robinish

New Member
Joined
Jul 22, 2013
Messages
3
I'm sorry, I didn't check your requirement well, try these formulas:

Book1
ABCDEFGHIJK
1Name CodeRateUnitsDISC 1DISC2DISC 3Total Discount
2ABCDE 10.525,4870.40.10.140.64ABCDE
3ABCDE 20.476,3120.420.10.140.66Weighted Avg Rate0.431
4ABCDE 30.389,1470.430.10.140.67Weighted Avg Total Discount0.646
5ABCDE 40.362,5680.360.10.140.6Loss/Gain6,444
6ABCDE 50.426,5890.380.10.140.62
7EFGHI 10.56,5830.320.20.140.66EFGHI
8EFGHI 20.237,8930.280.20.140.62Weighted Avg Rate0.343
9EFGHI 30.195,4890.350.20.140.69Weighted Avg Total Discount0.689
10EFGHI 40.381,2580.380.20.140.72Loss/Gain11,087
11EFGHI 50.429,6570.40.20.140.74
12EFGHI 60.281,2370.50.20.140.84ABCDE & EFGHI Combined
13Weighted Avg Rate0.386
14Weighted Avg Total Discount0.668
15Loss/Gain17,531
sheet
Cell Formulas
RangeFormula
K3K3=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)
K4K4=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)
K5K5=($K$4-$K$3)*SUMIF($A$2:$A$12,$J$2&"*",$C$2:$C$12)
K8K8=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)
K9K9=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)
K10K10=($K$9-$K$8)*SUMIF($A$2:$A$12,$J$7&"*",$C$2:$C$12)
K13K13=SUMPRODUCT(($B$2:$B$12)*($C$2:$C$12))/SUM($C$2:$C$12)
K14K14=SUMPRODUCT(($G$2:$G$12)*($C$2:$C$12))/SUM($C$2:$C$12)
K15K15=($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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top