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

#### trippypenguin

##### New Member
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!

##### Board Regular
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
ABCDEF
1SalesincentivesFromTo
28004.85%Sales500800
33425.15%
42883.09%
53005.15%arithmetic meanweighted average
61005.67%4.28%4.43%
72963.61%
84094.00%
92644.12%
102923.39%
111125.30%
124625.25%
132542.37%
1495930.80%
151826.18%
163065.15%
172023.59%
181725.15%
194205.12%
206044.57%
213005.00%
222024.44%
235092.13%
241406.18%
256545.58%
261804.64%
2710805.05%
283962.02%
29905.56%
303642.57%
319004.50%
32508.00%
332164.64%
342045.15%
354803.50%
Sheet1 (2)
Cell Formulas
RangeFormula
E6E6=AVERAGEIFS(B2:B35,A2:A35,">="&E2,A2:A35,"<="&F2)
F6F6=SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),A2:A35,B2:B35)/SUMIFS(A2:A35,A2:A35,">="&E2,A2:A35,"<="&F2)

Regards

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Fluff

##### MrExcel MVP, Moderator
The OP is using 2016, which doesn't have the AVERAGEIFS function.

##### Board Regular
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

##### Board Regular
@trippypenguin

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

Book2
ABCDEF
1SalesincentivesFromTo
28004.85%Sales500800
33425.15%
42883.09%
53005.15%arithmetic meanweighted average
61005.67%4.28%4.43%
72963.61%
84094.00%
92644.12%
102923.39%
111125.30%
124625.25%
132542.37%
1495930.80%
151826.18%
163065.15%
172023.59%
181725.15%
194205.12%
206044.57%
213005.00%
222024.44%
235092.13%
241406.18%
256545.58%
261804.64%
2710805.05%
283962.02%
29905.56%
303642.57%
319004.50%
32508.00%
332164.64%
342045.15%
354803.50%
Sheet2
Cell Formulas
RangeFormula
E6E6=SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),B2:B35)/SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2))
F6F6=SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),A2:A35,B2:B35)/SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),A2:A35)

Regards

#### trippypenguin

##### New Member
@trippypenguin

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

Book2
ABCDEF
1SalesincentivesFromTo
28004.85%Sales500800
33425.15%
42883.09%
53005.15%arithmetic meanweighted average
61005.67%4.28%4.43%
72963.61%
84094.00%
92644.12%
102923.39%
111125.30%
124625.25%
132542.37%
1495930.80%
151826.18%
163065.15%
172023.59%
181725.15%
194205.12%
206044.57%
213005.00%
222024.44%
235092.13%
241406.18%
256545.58%
261804.64%
2710805.05%
283962.02%
29905.56%
303642.57%
319004.50%
32508.00%
332164.64%
342045.15%
354803.50%
Sheet2
Cell Formulas
RangeFormula
E6E6=SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2),B2:B35)/SUMPRODUCT((A2:A35>=E2)*(A2:A35<=F2))
F6F6=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

Replies
5
Views
95
Replies
8
Views
75
Replies
3
Views
79
Replies
1
Views
255
Replies
33
Views
482

1,127,387
Messages
5,624,402
Members
416,026
Latest member
melvic69

### 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.

### Which adblocker are you using?

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

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