Advanced SUMPRODUCT function for multiple weighted averages

[gigglesxx]

Hi

I am an analyst and I need help with an advance use of the sum product function for weighted averages.

I am trying to calculate three different weighted averages within 1 of 50 groups of data. Each of the 50 groups of data have a different weighting score (different importance). I have tried to use the basic =sumproduct(A1:A16,B1:B16)/sum(B1:B16) however this only takes into account the weighting score of just group 1 (group 1 range is from A1:A16) however does not adjust the averages according to the weighting of the other 49 data groups.

PLEASE HELP ME

I am relatively new to excel.

 

[cunningAce]

Can you post sample data?

 

[gigglesxx]

Hi

Thank you for replying.

I am not sure how to attach a file on this forum, however i have copied and pasted my data into this reply to give you a rough idea.

Just to clarify, I would like to find out the weighted average of group 1 (1.1, 1.2 and 1.3) for each class, taking into account the weighting score of this group in relation to the other groups and their score of importance.

I have tried =sumproduct(F2:F4,D2:D4)/sum(D2:D4) however this only takes into account the weight score 5 and does not compare it to the other groups and their weighting scores.

Is there a way i can expand the formula to include the other scores?

Or is the =sumproduct function not the right function for this calculation and maybe i should use something else?

Any help would be much appreciated

NUMBER	ORIGIN	DESTINATION	WEIGHTING SCORE (0 - 5)	CLASS	COST
1.1	MELBOURNE	DESTINATION 1	5	FIRST	$ 100.00
1.2	MELBOURNE	DESTINATION 1	5	BUSINESS	$ 86.00
1.3	MELBOURNE	DESTINATION 1	5	ECONOMY	-$ 31.00
2.1	MELBOURNE	DESTINATION 2	2	FIRST	$ 234.00
2.2	MELBOURNE	DESTINATION 2	2	BUSINESS	-$ 41.00
2.3	MELBOURNE	DESTINATION 2	2	ECONOMY	-$ 104.00
3.1	MELBOURNE	DESTINATION 3	3	FIRST	$ 2,016.00
3.2	MELBOURNE	DESTINATION 3	3	BUSINESS	$ 1,056.00
3.3	MELBOURNE	DESTINATION 3	3	ECONOMY	-$ 900.00
4.1	MELBOURNE	DESTINATION 4	2	FIRST	-$ 900.00
4.2	MELBOURNE	DESTINATION 4	2	BUSINESS	$ 2,991.00
4.3	MELBOURNE	DESTINATION 4	2	ECONOMY	$ 2,116.00
5.1	MELBOURNE	DESTINATION 5	1	FIRST	$ 273.00
5.2	MELBOURNE	DESTINATION 5	1	BUSINESS	$ 3.00
5.3	MELBOURNE	DESTINATION 5	1	ECONOMY	-$ 171.00
6.1	MELBOURNE	DESTINATION 6	2	FIRST	$ 2,766.00
6.2	MELBOURNE	DESTINATION 6	2	BUSINESS	-$ 900.00
6.3	MELBOURNE	DESTINATION 6	2	ECONOMY	$ 1,991.00
7.1	MELBOURNE	DESTIANTION 7	4	FIRST	$ 7,877.00
7.2	MELBOURNE	DESTIANTION 7	4	BUSINESS	$ 6,092.00
7.3	MELBOURNE	DESTIANTION 7	4	ECONOMY	$ 4,065.00
8.1	MELBOURNE	DESTINATION 8	4	FIRST	$ 7,227.00
8.2	MELBOURNE	DESTINATION 8	4	BUSINESS	$ 5,342.00
8.3	MELBOURNE	DESTINATION 8	4	ECONOMY	$ 4,368.00
9.1	MELBOURNE	DESTINATION 9	4	FIRST	$ 8,127.00
9.2	MELBOURNE	DESTINATION 9	4	BUSINESS	$ 6,192.00
9.3	MELBOURNE	DESTINATION 9	4	ECONOMY	$ 4,142.00
10.1	MELBOURNE	DESTINATION 10	4	FIRST	$ 6,636.00
10.2	MELBOURNE	DESTINATION 10	4	BUSINESS	$ 4,961.00
10.3	MELBOURNE	DESTINATION 10	4	ECONOMY	$ 2,901.00
11.1	MELBOURNE	DESTINATION 11	4	FIRST	$ 7,627.00
11.2	MELBOURNE	DESTINATION 11	4	BUSINESS	$ 5,842.00
11.3	MELBOURNE	DESTINATION 11	4	ECONOMY	$ 3,792.00
12.1	MELBOURNE	DESTINATION 12	3	FIRST	$ 8,590.00
12.2	MELBOURNE	DESTINATION 12	3	BUSINESS	$ 7,696.00
12.3	MELBOURNE	DESTINATION 12	3	ECONOMY	-$ 900.00
13.1	MELBOURNE	DESTINATION 13	4	FIRST	$ 7,220.00
13.2	MELBOURNE	DESTINATION 13	4	BUSINESS	$ 5,210.00
13.3	MELBOURNE	DESTINATION 13	4	ECONOMY	$ 3,160.00
14.1	MELBOURNE	DESTINATION 14	3	FIRST	$ 7,002.00
14.2	MELBOURNE	DESTINATION 14	3	BUSINESS	$ 5,317.00
14.3	MELBOURNE	DESTINATION 14	3	ECONOMY	$ 4,787.00
15.1	MELBOURNE	DESTINATION 15	3	FIRST	$ 8,820.00
15.2	MELBOURNE	DESTINATION 15	3	BUSINESS	$ 7,646.00
15.3	MELBOURNE	DESTINATION 15	3	ECONOMY
					-$ 900.00
16.1	MELBOURNE	DESTINATION 16	3	FIRST	$ 5,456.00
16.2	MELBOURNE	DESTINATION 16	3	BUSINESS
16.3	MELBOURNE	DESTINATION 16	3	ECONOMY	-$ 900.00
17.1	MELBOURNE	DESTINATION 17	3	FIRST	$ 5,681.00
17.2	MELBOURNE	DESTINATION 17	3	BUSINESS	$ 4,236.00
17.3	MELBOURNE	DESTINATION 17	3	ECONOMY	$ 3,056.00
18.1	MELBOURNE	DESTINATION 18	1	FIRST	-$ 900.00
18.2	MELBOURNE	DESTINATION 18	1	BUSINESS	$ 1,747.00
18.3	MELBOURNE	DESTINATION 18	1	ECONOMY	-$ 900.00
19.1	MELBOURNE	DESTINATION 19	3	FIRST	$ 6,520.00
19.2	MELBOURNE	DESTINATION 19	3	BUSINESS	$ 4,135.00
19.3	MELBOURNE	DESTINATION 19	3	ECONOMY	$ 3,735.00
20.1	MELBOURNE	DESTINATION 20	3	FIRST	$ 5,696.00
20.2	MELBOURNE	DESTINATION 20	3	BUSINESS	$ 4,251.00
20.3	MELBOURNE	DESTINATION 20	3	ECONOMY	$ 3,571.00
21.1	MELBOURNE	DESTINATION 21	3	FIRST	$ 8,590.00
21.2	MELBOURNE	DESTINATION 21	3	BUSINESS	-$ 900.00
21.3	MELBOURNE	DESTINATION 21	3	ECONOMY	$ 6,535.00
22.1	MELBOURNE	DESTINATION 22	1	FIRST	$ 2,497.00
22.2	MELBOURNE	DESTINATION 22	1	BUSINESS	$ 1,897.00
22.3	MELBOURNE	DESTINATION 22	1	ECONOMY	$ 1,297.00
23.1	MELBOURNE	DESTINATION 23	3	FIRST	-$ 900.00
23.2	MELBOURNE	DESTINATION 23	3	BUSINESS	$ 8,590.00
23.3	MELBOURNE	DESTINATION 23	3	ECONOMY	-$ 900.00
24.1	MELBOURNE	DESTINATION 24	0	FIRST	$ 1,135.00
24.2	MELBOURNE	DESTINATION 24	0	BUSINESS	$ 860.00
24.3	MELBOURNE	DESTINATION 24	0	ECONOMY	-$ 900.00
25.1	MELBOURNE	DESTINATION 25	1	FIRST	$ 3,909.00
25.2	MELBOURNE	DESTINATION 25	1	BUSINESS	$ 1,722.00
25.3	MELBOURNE	DESTINATION 25	1	ECONOMY	-$ 900.00
26.1	MELBOURNE	DESTINATION 26	0	FIRST	$ 2,191.00
26.2	MELBOURNE	DESTINATION 26	0	BUSINESS	$ 1,591.00
26.3	MELBOURNE	DESTINATION 26	0	ECONOMY	$ 1,141.00
27.1	MELBOURNE	DESTINATION 27	1	FIRST	$ 1,843.00
27.2	MELBOURNE	DESTINATION 27	1	BUSINESS	$ 1,113.00
27.3	MELBOURNE	DESTINATION 27	1	ECONOMY	-$ 900.00
28.1	MELBOURNE	DESTINATION 28	1	FIRST	$ 2,022.00
28.2	MELBOURNE	DESTINATION 28	1	BUSINESS	$ 1,062.00
28.3	MELBOURNE	DESTINATION 28	1	ECONOMY	$ 735.00
29.1	MELBOURNE	DESTINATION 29	2	FIRST	$ 7,212.00
29.2	MELBOURNE	DESTINATION 29	2	BUSINESS	$ 4,836.00
29.3	MELBOURNE	DESTINATION 29	2	ECONOMY	$ 4,436.00
30.1	MELBOURNE	DESTINATION 30	2	FIRST	-$ 900.00
30.2	MELBOURNE	DESTINATION 30	2	BUSINESS	$ 8,590.00
30.3	MELBOURNE	DESTINATION 30	2	ECONOMY	-$ 900.00
31.1	MELBOURNE	DESTINATION 31	0	FIRST	$ 1,060.00
31.2	MELBOURNE	DESTINATION 31	0	BUSINESS	$ 667.00
31.3	MELBOURNE	DESTINATION 31	0	ECONOMY	-$ 900.00
32.1	MELBOURNE	DESTINATION 32	2	FIRST	$ 7,002.00
32.2	MELBOURNE	DESTINATION 32	2	BUSINESS	$ 5,317.00
32.3	MELBOURNE	DESTINATION 32	2	ECONOMY	-$ 900.00
33.1	MELBOURNE	DESTINATION 33	1	FIRST	-$ 900.00
33.2	MELBOURNE	DESTINATION 33	1	BUSINESS	$ 8,590.00
33.3	MELBOURNE	DESTINATION 33	1	ECONOMY	-$ 900.00
34.1	MELBOURNE	DESTINATION 34	1	FIRST	-$ 900.00
34.2	MELBOURNE	DESTINATION 34	1	BUSINESS	$ 3,485.00
34.3	MELBOURNE	DESTINATION 34	1	ECONOMY	-$ 900.00
35.1	MELBOURNE	DESTINATION 35	3	FIRST	$ 7,627.00
35.2	MELBOURNE	DESTINATION 35	3	BUSINESS	$ 5,942.00
35.3	MELBOURNE	DESTINATION 35	3	ECONOMY	$ 3,892.00
36.1	MELBOURNE	DESTINATION 36	2	FIRST	$ 8,590.00
36.2	MELBOURNE	DESTINATION 36	2	BUSINESS	$ 8,064.00
36.3	MELBOURNE	DESTINATION 36	2	ECONOMY	-$ 900.00
37.1	MELBOURNE	DESTINATION 37	0	FIRST	$ 2,364.00
37.2	MELBOURNE	DESTINATION 37	0	BUSINESS	$ 1,927.00
37.3	MELBOURNE	DESTINATION 37	0	ECONOMY	$ 912.00
38.1	MELBOURNE	DESTINATION 38	0	FIRST	$ 791.00
38.2	MELBOURNE	DESTINATION 38	0	BUSINESS	$ 341.00
38.3	MELBOURNE	DESTINATION 38	0	ECONOMY	-$ 900.00
39.1	MELBOURNE	DESTINATION 39	0	FIRST	-$ 900.00
39.2	MELBOURNE	DESTINATION 39	0	BUSINESS	$ 1,391.00
39.3	MELBOURNE	DESTINATION 39	0	ECONOMY	-$ 900.00
40.1	MELBOURNE	DESTINATION 40	2	FIRST	$ 7,877.00
40.2	MELBOURNE	DESTINATION 40	2	BUSINESS	$ 6,192.00
40.3	MELBOURNE	DESTINATION 40	2	ECONOMY	$ 4,142.00
41.1	MELBOURNE	DESTINATION 41	1	FIRST	$ 8,590.00
41.2	MELBOURNE	DESTINATION 41	1	BUSINESS	$ 7,754.00
41.3	MELBOURNE	DESTINATION 41	1	ECONOMY	-$ 900.00
42.1	MELBOURNE	DESTINATION 42	2	FIRST	$ 8,590.00
42.2	MELBOURNE	DESTINATION 42	2	BUSINESS	$ 7,668.00
42.3	MELBOURNE	DESTINATION 42	2	ECONOMY	-$ 900.00
43.1	MELBOURNE	DESTINATION 43	0	FIRST	$ 1,966.00
43.2	MELBOURNE	DESTINATION 43	0	BUSINESS	$ 1,191.00
43.3	MELBOURNE	DESTINATION 43	0	ECONOMY	-$ 900.00
44.1	MELBOURNE	DESTINATION 44	1	FIRST	$ 4,752.00
44.2	MELBOURNE	DESTINATION 44	1	BUSINESS	$ 3,202.00
44.3	MELBOURNE	DESTINATION 44	1	ECONOMY	-$ 900.00
45.1	MELBOURNE	DESTINATION 45	0	FIRST	$ 2,491.00
45.2	MELBOURNE	DESTINATION 45	0	BUSINESS	$ 1,891.00
45.3	MELBOURNE	DESTINATION 45	0	ECONOMY	$ 891.00
46.1	MELBOURNE	DESTINATION 46	0	FIRST	$ 211.00
46.2	MELBOURNE	DESTINATION 46	0	BUSINESS	-$ 64.00
46.3	MELBOURNE	DESTINATION 46	0	ECONOMY	-$ 270.00
47.1	MELBOURNE	DESTINATION 47	2	FIRST	$ 7,627.00
47.2	MELBOURNE	DESTINATION 47	2	BUSINESS	$ 5,692.00
47.3	MELBOURNE	DESTINATION 47	2	ECONOMY	$ 3,642.00
48.1	MELBOURNE	DESTINATION 48	2	FIRST	$ 7,877.00
48.2	MELBOURNE	DESTINATION 48	2	BUSINESS	$ 6,192.00
48.3	MELBOURNE	DESTINATION 48	2	ECONOMY	$ 4,142.00
49.1	MELBOURNE	DESTINATION 49	0	FIRST	$ 7,086.00
49.2	MELBOURNE	DESTINATION 49	0	BUSINESS	$ 5,996.00
49.3	MELBOURNE	DESTINATION 49	0	ECONOMY	-$ 900.00
50.1	MELBOURNE	DESTINATION 50	1	FIRST	$ 8,590.00
50.2	MELBOURNE	DESTINATION 50	1	BUSINESS	$ 7,664.00
50.3	MELBOURNE	DESTINATION 37	1	ECONOMY	-$ 900.00

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[cunningAce]

Hi,

Not sure I fully understand what it is you want to accomplish.
Can you show me an example of what the result would look like?

 

[gigglesxx]

Hi,

I realise I wasn't very clear on what i want. I have fixed up the data:

WEIGHTING SCORE (0-5)	ORIGIN	DESTINATION	CLASS	COST	AVERAGE	WEIGHTED AVERAGE
5	MELBOURNE	LONDON	FIRST full flexi	$ 3,000.00	$ 2,500.00	$ 3,300.00
5			FIRST flexi	$ 2,500.00
5			FIRST restricted	$ 2,000.00
5	MELBOURNE	LONDON	BUSINESS full flexi	$ 1,800.00	$ 1,433.33	$ 1,850.00
5			BUSINESS flexi	$ 1,500.00
5			BUSINESS restricted	$ 1,000.00
5	MELBOURNE	LONDON	ECONOMY full flexi	$ 900.00	$ 700.00	$ 950.00
5			ECONOMY flexi	$ 700.00
5			ECONOMY restricted	$ 500.00
2	MELBOURNE	BERLIN	FIRST full flexi	$ 3,500.00	$ 3,333.33	$ 3,100.00
2			FIRST flexi	$ 3,300.00
2			FIRST restricted	$ 3,200.00
2	MELBOURNE	BERLIN	BUSINESS full flexi	$ 3,000.00	$ 2,733.33	$ 1,650.00
2			BUSINESS flexi	$ 2,700.00
2			BUSINESS restricted	$ 2,500.00
2	MELBOURNE	BERLIN	ECONOMY full flexi	$ 2,000.00	$ 1,766.67	$ 1,600.00
2			ECONOMY flexi	$ 1,800.00
2			ECONOMY restricted	$ 1,500.00
3	MELBOURNE	SINGAPORE	FIRST full flexi	$ 1,500.00	$ 1,400.00	$ 1,450.00
3			FIRST flexi	$ 1,400.00
3			FIRST restricted	$ 1,300.00
3	MELBOURNE	SINGAPORE	BUSINESS full flexi	$ 1,000.00	$ 916.67	$ 1,000.00
3			BUSINESS flexi	$ 900.00
3			BUSINESS restricted	$ 850.00
3	MELBOURNE	SINGAPORE	ECONOMY full flexi	$ 700.00	$ 583.33	$ 700.00
3			ECONOMY flexi	$ 600.00
3			ECONOMY restricted	$ 450.00
1	MELBOURNE	HONG KONG	FIRST full flexi	$ 4,000.00	$ 3,800.00	$ 3,400.00
1			FIRST flexi	$ 3,900.00
1			FIRST restricted	$ 3,500.00
1	MELBOURNE	HONG KONG	BUSINESS full flexi	$ 3,000.00	$ 2,500.00	$ 2,450.00
1			BUSINESS flexi	$ 2,500.00
1			BUSINESS restricted	$ 2,000.00
1	MELBOURNE	HONG KONG	ECONOMY full flexi	$ 1,900.00	$ 1,466.67	$ 1,300.00
1			ECONOMY flexi	$ 1,500.00
1			ECONOMY restricted	$ 1,000.00
4	MELBOURNE	SYDNEY	FIRST full flexi	$ 1,000.00	$ 1,566.67	$ 1,700.00
4			FIRST flexi	$ 1,900.00
4			FIRST restricted	$ 1,800.00
4	MELBOURNE	SYDNEY	BUSINESS full flexi	$ 1,600.00	$ 1,466.67	$ 1,600.00
4			BUSINESS flexi	$ 1,500.00
4			BUSINESS restricted	$ 1,300.00
4	MELBOURNE	SYDNEY	ECONOMY full flexi	$ 1,000.00	$ 833.33	$ 950.00
4			ECONOMY flexi	$ 850.00
4			ECONOMY restricted	$ 650.00
				TOTAL	$ 27,000.00	$ 27,000.00

<colgroup><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

The figures I am trying to calculate are the bold ones. If you sum them up, they would equate to the same total as the normal average calculation, however they increase and decrease (per travel class) depending on the importance of the group. Does this make better sense?

Thank you so much for trying to help

 

[Aladin Akyurek]

How does the very first value of 3,300.00 obtain? That is, what is your manual calculation?