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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi Dear,

I am sure you did your best in articulating the question. I would like to help, but I am finding difficulties in understanding your exact requirement. Thus, I am assuming you will be selecting a specific range of the data set, and then you want to the the weighted-average percentage. Therefore, please try the following (copy and paste in your sheet, and change the input cells, G4 and G5):

forum.xlsx
ABCDEFGHI
1ABCFees
2 800 5,544 6,573 4.36%
3 342 1,710 2,529 1.55%
4 288 1,463 1,756 1.18%start cell A2
5 300 2,314 6,360 3.03%end cell B4
6 100 1,101 2,759 1.34%A2:B4 10,147
7 296 2,257 18,341 7.06%
8 409 1,373 1,594 1.14%
9 264 1,561 1,852 1.24%ABC%
10 292 982 6,773 2.72%800554462.52%
11 112 659 1,344 0.71%342171020.22%
12 462 1,855 2,366 1.58%288146317.26%
13 254 1,398 1,776 1.16%
14 9,593 38,373 116,687 55.60%
15 182 1,411 1,197 0.94%
16 306 1,996 4,774 2.39%
17 202 807 1,004 0.68%
18 172 1,524 1,602 1.11%
19 420 3,317 2,781 2.20%
20 604 4,039 17,582 7.50%
21 300 2,781 4,341 2.51%
Sheet3
Cell Formulas
RangeFormula
G6G6=G4&":"&G5
H6H6=SUM(INDIRECT(G6))
F10:G12F10=INDIRECT(G6)
I10:I12I10=SUM(F10:H10)/$H$6
D2:D21D2=SUM(A2:C2)/SUM($A$2:$C$21)
Dynamic array formulas.


Regards
 
Upvote 0
Thanks for your reply mamady! You certainly helped me learn something new - I guess I could use this whenever I want to select the range of data that I want to manipulate myself.

To be a little more clear with my initial intention, I wanted to select a column range of data with regards to certain conditions - such as: (in the column A) "cells with values between 100 and 200" , instead of manually selecting a continuous range of cells, and find the weighted average fee % only considering those cells!

I hope that made it a little more clear - I should've worded it better the first time.
Could you possibly help me with that as well?

Much appreciated,
 
Upvote 0
Sure dear, I think I am getting your request. Another question: Based on your example, upon specifying the condition of column A, should the adjacent values in column B and C be retrieved as part of the weighted average?

Regards
 
Upvote 0
Try the below please, by changing the inputs values in cells F2, G2, H2:

Book2.xlsx
ABCDEFGHI
1ABCFeesFromTo
2800554465734.36%A200700
3342171025291.55%
4288146317561.18%ABC%
5300231463603.03%342171025294.30%
6100110127591.34%288146317563.30%
72962257183417.06%300231463608.43%
8409137315941.14%29622571834119.63%
9264156118521.24%409137315943.17%
1029298267732.72%264156118523.46%
1111265913440.71%29298267737.56%
12462185523661.58%462185523664.40%
13254139817761.16%254139817763.22%
1495933837311668755.60%306199647746.65%
15182141111970.94%20280710041.89%
16306199647742.39%420331727816.12%
1720280710040.68%60440391758220.88%
18172152416021.11%300278143416.97%
19420331727812.20%
206044039175827.50%
21300278143412.51%
Sheet1
Cell Formulas
RangeFormula
F5:F18F5=CHOOSE(MATCH($F$2,$F$4:$H$4),FILTER($A$2:$A$21,($A$2:$A$21>=G2)*($A$2:$A$21<=H2)),FILTER($A$2:$A$21,($B$2:$B$21>=G2)*($B$2:$B$21<=H2)),FILTER($A$2:$A$21,($C$2:$C$21>=G2)*($C$2:$C$21<=H2)))
G5:G18G5=CHOOSE(MATCH($F$2,$F$4:$H$4),FILTER($B$2:$B$21,($A$2:$A$21>=G2)*($A$2:$A$21<=H2)),FILTER($B$2:$B$21,($B$2:$B$21>=G2)*($B$2:$B$21<=H2)),FILTER($B$2:$B$21,($C$2:$C$21>=G2)*($C$2:$C$21<=H2)))
H5:H18H5=CHOOSE(MATCH($F$2,$F$4:$H$4),FILTER($C$2:$C$21,($A$2:$A$21>=G2)*($A$2:$A$21<=H2)),FILTER($C$2:$C$21,($B$2:$B$21>=G2)*($B$2:$B$21<=H2)),FILTER($C$2:$C$21,($C$2:$C$21>=G2)*($C$2:$C$21<=H2)))
I5:I18I5=SUM(F5:H5)/SUM($F$5:$H$100)
D2:D21D2=SUM(A2:C2)/SUM($A$2:$C$21)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
F2ListA,B,C


Regards
 
Upvote 0
Try the below please, by changing the inputs values in cells F2, G2, H2:

Book2.xlsx
ABCDEFGHI
1ABCFeesFromTo
2800554465734.36%A200700
3342171025291.55%
4288146317561.18%ABC%
5300231463603.03%342171025294.30%
6100110127591.34%288146317563.30%
72962257183417.06%300231463608.43%
8409137315941.14%29622571834119.63%
9264156118521.24%409137315943.17%
1029298267732.72%264156118523.46%
1111265913440.71%29298267737.56%
12462185523661.58%462185523664.40%
13254139817761.16%254139817763.22%
1495933837311668755.60%306199647746.65%
15182141111970.94%20280710041.89%
16306199647742.39%420331727816.12%
1720280710040.68%60440391758220.88%
18172152416021.11%300278143416.97%
19420331727812.20%
206044039175827.50%
21300278143412.51%
Sheet1
Cell Formulas
RangeFormula
F5:F18F5=CHOOSE(MATCH($F$2,$F$4:$H$4),FILTER($A$2:$A$21,($A$2:$A$21>=G2)*($A$2:$A$21<=H2)),FILTER($A$2:$A$21,($B$2:$B$21>=G2)*($B$2:$B$21<=H2)),FILTER($A$2:$A$21,($C$2:$C$21>=G2)*($C$2:$C$21<=H2)))
G5:G18G5=CHOOSE(MATCH($F$2,$F$4:$H$4),FILTER($B$2:$B$21,($A$2:$A$21>=G2)*($A$2:$A$21<=H2)),FILTER($B$2:$B$21,($B$2:$B$21>=G2)*($B$2:$B$21<=H2)),FILTER($B$2:$B$21,($C$2:$C$21>=G2)*($C$2:$C$21<=H2)))
H5:H18H5=CHOOSE(MATCH($F$2,$F$4:$H$4),FILTER($C$2:$C$21,($A$2:$A$21>=G2)*($A$2:$A$21<=H2)),FILTER($C$2:$C$21,($B$2:$B$21>=G2)*($B$2:$B$21<=H2)),FILTER($C$2:$C$21,($C$2:$C$21>=G2)*($C$2:$C$21<=H2)))
I5:I18I5=SUM(F5:H5)/SUM($F$5:$H$100)
D2:D21D2=SUM(A2:C2)/SUM($A$2:$C$21)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
F2ListA,B,C


Regards
Wow , things suddenly got a lot more complicated...!

Thanks so much for the effort - and to answer your question, columns A/B/C represent different categories - such as height, weight and age (I guess in this particular analogy, we could think of D as long-jump distance to complete the story).

I could possibly need to retrieve the adjacent column values in the future, and I'm sure I will come across a situation like that, but for now I'm simply trying to find out how the data in column D (jump distance) correlates on average to each of the different categories (height etc) within a certain range of their values.

May I please ask for a final favor to get a single cell with a weighted average % for only the values in category A within the example range you used earlier (200~700), without having to make another data block on the sheet? (if it is possible)

Thank you again,
 
Upvote 0
Sure Dear,

Please find the below:

Book2.xlsx
ABCDEFGH
1ABCFeesFromTo
2800554465734.36%A200700
3342171025291.55%
4288146317561.18%A%
5300231463603.03%3427.22%
6100110127591.34%2886.08%
72962257183417.06%3006.33%
8409137315941.14%2966.25%
9264156118521.24%4098.63%
1029298267732.72%2645.57%
1111265913440.71%2926.16%
12462185523661.58%4629.75%
13254139817761.16%2545.36%
1495933837311668755.60%3066.46%
15182141111970.94%2024.26%
16306199647742.39%4208.86%
1720280710040.68%60412.75%
18172152416021.11%3006.33%
19420331727812.20%
206044039175827.50%
21300278143412.51%
Sheet1
Cell Formulas
RangeFormula
F5:F18F5=FILTER($A$2:$A$21,($A$2:$A$21>=G2)*($A$2:$A$21<=H2))
G5:G18G5=F5/SUM($F$5#)
D2:D21D2=SUM(A2:C2)/SUM($A$2:$C$21)
Dynamic array formulas.


Regards
 
Upvote 0
Hi Mamady, thank you again for your reply. I honestly do not want to bother you anymore with this problem and you've done god's work in helping me out so far, but I would like to have one last attempt at reiterating the issue through excel and a simpler example with only 2 columns (I should've done this from the start... I apologize).

I've written down the descriptions in the sheet, I would greatly appreciate it if you could have one last try at this, if it is a possible task (I asked around, and heard things about index/match... not sure if that is applicable here)

Thank you so much again.

1612680937117.png
 
Upvote 0
Hi Mamady, thank you again for your reply. I honestly do not want to bother you anymore with this problem and you've done god's work in helping me out so far, but I would like to have one last attempt at reiterating the issue through excel and a simpler example with only 2 columns (I should've done this from the start... I apologize).

I've written down the descriptions in the sheet, I would greatly appreciate it if you could have one last try at this, if it is a possible task (I asked around, and heard things about index/match... not sure if that is applicable here)

Thank you so much again.

View attachment 31435

Hi Dear,

Don't worry I will keep trying to help till you get what you need.
Your requirement is much clearer to me now. Thanks for sharing the screen-shot.

1- I am getting different values for the arithmetic mean and weighted average in the example you shared:

1612683469444.png


1612683549291.png


for the avoidance of doubt, the below is a manual calculation to cross-check

wieghted average.xlsx
EFGH
17SalesincentivesSales weight %(Sales weight %)*(incentive %)
18508.52.13%17%0.36%
196545.58%22%1.22%
206044.57%20%0.92%
217202.04%24%0.49%
225092.13%17%0.36%
23
24sum (weighted average)3.35%
25average (arithmetic mean)3.29%
Sheet1
Cell Formulas
RangeFormula
G18:G22G18=E18/SUM($E$18:$E$22)
H18:H22H18=G18*F18
H24H24=SUM(H18:H22)
H25H25=AVERAGE(F18:F22)


2- if my calculation is fine with you, then you can use the below formulas. You don't need Index/Match if your MS Excel version support Filter function (let me know if it doesn't)

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
Cell Formulas
RangeFormula
E6E6=AVERAGE(FILTER($B$2:$B$35,($A$2:$A$35>=E2)*($A$2:$A$35<=F2)))
F6F6=SUMPRODUCT(FILTER($A$2:$A$35,($A$2:$A$35>=E2)*($A$2:$A$35<=F2)),FILTER($B$2:$B$35,($A$2:$A$35>=E2)*($A$2:$A$35<=F2)))/SUM(FILTER($A$2:$A$35,($A$2:$A$35>=E2)*($A$2:$A$35<=F2)))


Regards
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top