weighted sum

blue333 · Jun 1, 2018

Hi

I need some help calculating a weighted sum. I have an example which shows how it should be done. Any help is great appreciated! Thanks in advance

Sample Data:

	A	B	C	D	E	F	G	Total
B		48%	5%	33%			14%	100%
C	16%	2%	5%	9%	21%	23%	23%	100%
F	13%			35%	30%		22%	100%
H	26%		39%		17%	17%		100%
Z	32%			4%		36%	28%	100%

	April	May	June	July	August	September
A	65	21	16	60	98	83
B	95	19	90	43	24	46
C	14	3	68	76	15	14
D	47	71	15	6	51	80
E	52	57	40	65	49	79
F	23	87	70	63	16	94
G	47	91	59	63	21	6
H	22	22	70	72	3	72
Z	94	73	20	76	33	59

Expected Outcome:

	April	May	June	July	August	September
B	68.29	45.86	59.52	35.10	32.14	50.10
C	44.98	63.93	47.63	57.77	40.81	62.47
F	50.87	64.57	32.30	43.39	50.00	64.00
H	35.48	31.70	49.91	67.65	42.74	57.22
Z	44.12	66.36	47.44	59.76	45.04	65.28

Sample Calculation:

68.29 = 0.48*95 + 0.05*14 + 0.33*47 + 0.14*47

Eric W · Jun 1, 2018

Maybe:

	A	B	C	D	E	F	G	H	I	K	L	M	N	O	P	Q	S	T	U	V	W	X	Y
1		A	B	C	D	E	F	G	Total		April	May	June	July	August	September		April	May	June	July	August	September
2	B		48%	5%	33%			14%	100%	A	65	21	16	60	98	83	B	68.39	45.44	59.81	35.24	32.04	50.02
3	C	16%	2%	5%	9%	21%	23%	23%	100%	B	95	19	90	43	24	46	C	44.25	63.19	47.18	57.43	40.3	61.69
4	F	13%			35%	30%		22%	100%	C	14	3	68	76	15	14	F	50.84	64.7	32.31	43.26	49.91	63.81
5	H	26%		39%		17%	17%		100%	D	47	71	15	6	51	80	H	35.11	31.11	49.38	67	42.38	56.45
6	Z	32%			4%		36%	28%	100%	E	52	57	40	65	49	79	Z	44.12	66.36	47.44	59.76	45.04	65.28
7										F	23	87	70	63	16	94
8										G	47	91	59	63	21	6
9										H	22	22	70	72	3	72
10										Z	94	73	20	76	33	59
11

Sheet4

Worksheet Formulas

Cell	Formula
T2	=MMULT(INDEX($B$2:$H$6,MATCH($S2,$A$2:$A$6,0),0)+0,OFFSET($L$2:$L$8,0,MATCH(T$1,$L$1:$Q$1,0)-1))

The numbers don't all match exactly, but they all seem to be in the ballpark. If you find one that's not right, let me know how it should be calculated.

joeu2004 · Jun 1, 2018

blue333 said:
Sample Calculation:
68.29 = 0.48*95 + 0.05*14 + 0.33*47 + 0.14*47

Your sample calculation is incorrect. It should be:

Code:

0.48*95 = 45.60
0.05*14 =  0.70
0.33*47 = 15.51
0.14*47 =  6.58
          -----
          68.39, not 68.29

The difference might be (a) your calculation error, or (b) due to rounding of either the percentages or (unlikely) the integers.

In any case, that might explain the differences in the results that I get below, which are the same as EricW's results.

Sample Data:

	A	B	C	D	E	F	G	H	I
1		A	B	C	D	E	F	G	Total
2	B		48%	5%	33%			14%	100%
3	C	16%	2%	5%	9%	21%	23%	23%	100%
4	F	13%			35%	30%		22%	100%
5	H	26%		39%		17%	17%		100%
6	Z	32%			4%		36%	28%	100%

	A	B	C	D	E	F	G
8		April	May	June	July	August	September
9	A	65	21	16	60	98	83
10	B	95	19	90	43	24	46
11	C	14	3	68	76	15	14
12	D	47	71	15	6	51	80
13	E	52	57	40	65	49	79
14	F	23	87	70	63	16	94
15	G	47	91	59	63	21	6
16	H	22	22	70	72	3	72
17	Z	94	73	20	76	33	59

Expected Outcome:

	A	B	C	D	E	F	G
19		April	May	June	July	August	September
20	B	68.39	45.44	59.81	35.24	32.04	50.02
21	C	44.25	63.19	47.18	57.43	40.30	61.69
22	F	50.84	64.70	32.31	43.26	49.91	63.81
23	H	35.11	31.11	49.38	67.00	42.38	56.45
24	Z	44.12	66.36	47.44	59.76	45.04	65.28

Formula:

B20: { =SUMPRODUCT(TRANSPOSE($B2:$H2), B$9:B$15) }

Copy B20 into B20:G24

Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

Eric W · Jun 2, 2018

joeu2004 is correct with his math. And looking at his formula, it occurs to me that I can simplify mine. I originally assumed that the headings on the side would not necessarily be in order, so I used MATCH to locate the proper row. But if they are always in order, you can simplify my formula to:

T2: =MMULT($B2:$H2+0,L$2:L$8+0)

Same results as post 2.

blue333 · Jun 2, 2018

Thank you both for your replies.

1. The sample calculation is "correct". I copied it from Excel but it had full decimal stored but only displayed the part without the decimal. When I copied it over to the forum it lost the decimals. I forgot to redo the expected results when the sample calculation was done. You guys are too sharp and noticed the mistake. Well done!

2. I got Eric's solution to work. I was going to try joeu2004 solution too until i realized something. This is perhaps due to the sample data I provided. No assumption should be made on the order of the letters in the columns or rows I have provided. It is a pure coincidence that they are "ABCDEFG". In practice, the letters are not guaranteed to be in any order. There could also be omissions (ie, ABDGJL).

Given this, how can the formulas be updated to actually search the letters? I have included revised examples with expected data. I cannot attach any files so I will expand the decimals to help get the expected results. If the results you get are close then that should be good enough.

Sample data:

	A	B	C	D	Z	F	G	Total
B		47.61905%	4.76190%	33.33333%			14.28571%	100%
C	16.27907%	2.32558%	4.65116%	9.30233%	20.93023%	23.25581%	23.25581%	100%
F	13.04348%			34.78261%	30.43478%		21.73913%	100%
H	26.08696%		39.13043%		17.39130%	17.39130%		100%
Z	32.00000%			4.00000%		36.00000%	28.00000%	100%

	2018-04	2018-05	2018-06	2018-07	2018-08	2018-09
A	65	21	16	60	98	83
B	95	19	90	43	24	46
C	14	3	68	76	15	14
D	47	71	15	6	51	80
E	52	57	40	65	49	79
F	23	87	70	63	16	94
G	47	91	59	63	21	6
H	22	22	70	72	3	72
Z	94	73	20	76	33	59

expected outcome

	2018-04	2018-05	2018-06	2018-07	2018-08	2018-09
B	68.29	45.86	59.52	35.10	32.14	50.10
C	53.77	67.28	43.44	60.07	37.47	58.28
F	63.65	69.43	26.22	46.74	45.13	57.91
H	42.78	34.48	46.43	69.57	39.96	53.74
Z	44.12	66.36	47.44	59.76	45.04	65.28

Thanks so much!!

Eric W · Jun 2, 2018

In that case, try:

Excel 2012

	A	B	C	D	E	F	G	H	I	K	L	M	N	O	P	Q	S	T	U	V	W	X	Y
1		A	B	C	D	Z	F	G	Total		2018-04	2018-05	2018-06	2018-07	2018-08	2018-09		2018-04	2018-05	2018-06	2018-07	2018-08	2018-09
2	B		47.62%	4.76%	33.33%			14.29%	100%	A	65	21	16	60	98	83	B	68.285712	45.85714	59.52381	35.09523	32.14285	50.09524
3	C	16.28%	2.33%	4.65%	9.30%	20.93%	23.26%	23.26%	100%	B	95	19	90	43	24	46	C	53.767437	67.27906	43.44185	60.06976	37.46512	58.27907
4	F	13.04%			34.78%	30.43%		21.74%	100%	C	14	3	68	76	15	14	F	63.652173	69.43478	26.21739	46.73913	45.13044	57.91304
5	H	26.09%		39.13%		17.39%	17.39%		100%	D	47	71	15	6	51	80	H	42.782605	34.47825	46.43478	69.56521	39.95652	53.73913
6	Z	32.00%			4.00%		36.00%	28.00%	100%	E	52	57	40	65	49	79	Z	44.12	66.36	47.44	59.76	45.04	65.28
7										F	23	87	70	63	16	94
8										G	47	91	59	63	21	6
9										H	22	22	70	72	3	72
10										Z	94	73	20	76	33	59

Sheet1

Worksheet Formulas

Cell	Formula
T2	=SUMPRODUCT(INDEX($B$2:$H$6,MATCH($S2,$A$2:$A$6,0),0),SUMIF($K$2:$K$10,$B$1:$H$1,L$2:L$10))

This assumes that the columns in tables 2 and 3 are in the same positions. If not, this version looks up the right column from table 2:

=SUMPRODUCT(INDEX($B$2:$H$6,MATCH($S2,$A$2:$A$6,0),0),SUMIF($K$2:$K$10,$B$1:$H$1,INDEX($L$2:$Q$10,0,MATCH(T$1,$L$1:$Q$1,0))))

blue333 · Jun 2, 2018

wow!
you are a genius!

it works beautifully

thank you so much!!!!!!!!!

Eric W · Jun 2, 2018

You're very welcome!

weighted sum

blue333

Board Regular

Excel Facts

Eric W

MrExcel MVP

joeu2004

Banned user

Eric W

MrExcel MVP

blue333

Board Regular

Eric W

MrExcel MVP

blue333

Board Regular

Eric W

MrExcel MVP

Similar threads

Forum statistics

Share this page

weighted sum

Board Regular

Excel Facts

MrExcel MVP

Banned user

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock