Weighted Average across rows for a specific column range of cells (by size of the numerical value)

[trippypenguin]

Hello dear community,

This is my first time posting a question on any online forum - I'm incredibly relieved to have come across a website like this.

I'm trying to find the weighted average(W.A) of fees by different categories (different columns) across rows, by specific range of values within the column.


I figured out how to get the W.A throughout the entire column (pretty straightforward) - I simply had to use sumproduct(column D, column A/B/C) / sum(column A/B/C) *A/B/C as in colulmns A or B or C - different categories

But, within the specific categories (columns A/B/C), I want to model scenarios for different range of values within each category (columns A/B/C).
E.g) Within column A/B/C (e.g. Height/Weight/etc), there would be 1,000 random values ranging from 150 to 210 - now I wish to find the weighted average fees for each category by a specific range of height
--> such as, weighted average fees for people that are between 150 and 160 cm tall.

How should I approach this? It was incredibly difficult for me to word this particular question concisely enough to post on google to get meaningful searches...

*Below is an example data range - I scraped this from my excel and it shows now but I'm not sure if you guys would be able to see it after I post it. I hope my question is clear!

 

[mamady]

That is exactly the result I needed! Thank you very much. The calculation should be different because I didn't post the entire set of data on the thread above.

One problem is that as you mentioned, my excel doesn't seem to support the filter function (I can only use filterxml here) - would it be possible to get a different equation that works on mine as well?

Thank you,

excellent, please find the below without the filter function:

wieghted average.xlsx
	A	B	C	D	E	F
1	Sales	incentives			From	To
2	800	4.85%		Sales	500	800
3	342	5.15%
4	288	3.09%
5	300	5.15%			arithmetic mean	weighted average
6	100	5.67%			4.28%	4.43%
7	296	3.61%
8	409	4.00%
9	264	4.12%
10	292	3.39%
11	112	5.30%
12	462	5.25%
13	254	2.37%
14	9593	0.80%
15	182	6.18%
16	306	5.15%
17	202	3.59%
18	172	5.15%
19	420	5.12%
20	604	4.57%
21	300	5.00%
22	202	4.44%
23	509	2.13%
24	140	6.18%
25	654	5.58%
26	180	4.64%
27	1080	5.05%
28	396	2.02%
29	90	5.56%
30	364	2.57%
31	900	4.50%
32	50	8.00%
33	216	4.64%
34	204	5.15%
35	480	3.50%
Sheet1 (2)

Cell Formulas
Range	Formula
E6	E6	=AVERAGEIFS(B2:B35,A2:A35,">="&E2,A2:A35,"<="&F2)
F6	F6	=SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),A2:A35,B2:B35)/SUMIFS(A2:A35,A2:A35,">="&E2,A2:A35,"<="&F2)

Regards

 

[Fluff]

@mamady
The OP is using 2016, which doesn't have the AVERAGEIFS function.

 

[mamady]

@mamady
The OP is using 2016, which doesn't have the AVERAGEIFS function.

Thank you @Fluff for bringing this to my attention, I keep forgetting which formulas work with which office version.
I shall provide an alternative formula

Regards

 

[mamady]

@trippypenguin

Sorry for the inconvenience, please find the below formulas, compatible with your office version:

Book2
	A	B	C	D	E	F
1	Sales	incentives			From	To
2	800	4.85%		Sales	500	800
3	342	5.15%
4	288	3.09%
5	300	5.15%			arithmetic mean	weighted average
6	100	5.67%			4.28%	4.43%
7	296	3.61%
8	409	4.00%
9	264	4.12%
10	292	3.39%
11	112	5.30%
12	462	5.25%
13	254	2.37%
14	9593	0.80%
15	182	6.18%
16	306	5.15%
17	202	3.59%
18	172	5.15%
19	420	5.12%
20	604	4.57%
21	300	5.00%
22	202	4.44%
23	509	2.13%
24	140	6.18%
25	654	5.58%
26	180	4.64%
27	1080	5.05%
28	396	2.02%
29	90	5.56%
30	364	2.57%
31	900	4.50%
32	50	8.00%
33	216	4.64%
34	204	5.15%
35	480	3.50%
Sheet2

Cell Formulas
Range	Formula
E6	E6	=SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),B2:B35)/SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2))
F6	F6	=SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),A2:A35,B2:B35)/SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),A2:A35)

Regards

 

[trippypenguin]

@trippypenguin

Sorry for the inconvenience, please find the below formulas, compatible with your office version:

Book2
	A	B	C	D	E	F
1	Sales	incentives			From	To
2	800	4.85%		Sales	500	800
3	342	5.15%
4	288	3.09%
5	300	5.15%			arithmetic mean	weighted average
6	100	5.67%			4.28%	4.43%
7	296	3.61%
8	409	4.00%
9	264	4.12%
10	292	3.39%
11	112	5.30%
12	462	5.25%
13	254	2.37%
14	9593	0.80%
15	182	6.18%
16	306	5.15%
17	202	3.59%
18	172	5.15%
19	420	5.12%
20	604	4.57%
21	300	5.00%
22	202	4.44%
23	509	2.13%
24	140	6.18%
25	654	5.58%
26	180	4.64%
27	1080	5.05%
28	396	2.02%
29	90	5.56%
30	364	2.57%
31	900	4.50%
32	50	8.00%
33	216	4.64%
34	204	5.15%
35	480	3.50%
Sheet2

Cell Formulas
Range	Formula
E6	E6	=SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),B2:B35)/SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2))
F6	F6	=SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),A2:A35,B2:B35)/SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),A2:A35)

Regards

Thank you so much for your help! Hope you have a fantastic day