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

trippypenguin

New Member
Joined
Feb 5, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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!

1612526336899.png
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
228
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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.

mamady

Board Regular
Joined
Sep 23, 2011
Messages
228
Office Version
  1. 365
Platform
  1. Windows
@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

Board Regular
Joined
Sep 23, 2011
Messages
228
Office Version
  1. 365
Platform
  1. Windows
@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
Joined
Feb 5, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
@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
 

Watch MrExcel Video

Forum statistics

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