Weighted Average Rankings dynamic selections of which values to count

diversification

New Member
Joined
Jun 24, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Here's my spreadsheet.

Objective:
Get a [cumulative] Weighted Ranking of Plants by including only specific individuals' ranks for plant types.

Disclaimers and needs to keep in mind:
1. No Visual Basic / Macros
2. I'm using Excel 2010, which means no FILTER, along with a few other functions
3. The actual data will have a LOT more names in the PPL table, and a lot more corresponding FirstnameLastname Sheets / Tables
4. There are likely to be additional columns & positions in all these sheets, which is why I'm using Structured References -- they're quite important here

Workbook Element 1: FirstnameLastname Tables / Sheets
The workbook contains a series of Sheets with people's names with the format 'FirstnameLastname'. Contained in each sheet is a Table with the same name as the Sheet. Each of those Sheets also contains a second table named "Weight1", "Weight2" and so on for each successive person's sheet.

Contained in the FirstnameLastname (hereafter abbreviated "FnLn") tables are:
1. A list of edible Plants
2. A rank for each plant
3. A 'type' that each plant falls into (Fruit, Vegetable, Seed, or Nut)
4. The product of the corresponding plant rank, and the corresponding category's weighting for that individual

The corresponding category's weighting are found in the "Weighting" table for each sheet. Those values are pull from the "PPL" table, contained in the "People" sheet. When a weighting (and thus a weight-adjusted rank) are showing as zero, that is because I've chosen to exclude that type of plants for that specific individual. In the example, Jake and Rob both have a type of plant excluded, while Sandy is the only one who is categorically included. You'll also notice that "Nut" type Plants are just included to provide excess data -- some types of plants (like nuts) are not intended to make it into the final weighted rankings at all.

Workbook Element 2: "PPL Table" / "People" Sheet
1. As mentioned, each person has different weightings for each of the included types (Fruit, Vegetable, and Seed,) and those weightings are the numerical values.
2. The "X" marks in the column to the right select whether or not that individual's rankings will be factored in for that particular type of plant.
3. The TableNames are derived values which dictate how the FnLn sheets & tables are named.
4. The TableExists looks for a table that names the corresponding TableNames value, and then either reproduces that value if it exists, or returns blank if it does not.

Workbook Element 3: Weighted Average Sheet / Master Table
Right now this sheet is a mess with my attempts to figure out ways to do this, so it can largely be ignored, save for Columns A - C (Plants, Rank & Type) which will be the constants here. From here I cannot figure out how to get the weighted average rank automated properly.

Here's a written description of my thoughts about how things work and the elements involved in getting to the Weighted Average Ranks.
For all tables that exist (ie. for all individuals listed in the People sheet whose rankings are in a sheet named accordingly) I need the "PPL" table to be checked for the "X"s that will determine which type of Plants will be considered in the calculation for that individual. For Types that are included, the weighting value for that type will be passed from the PPL table into the Weighting table of the corresponding FnLn sheet, and then the weighting values (including zeros for excluded types) will be multiplied against the Rank for each Plant to deliver a weighted rank. Those weighted ranks then need to be used along with the summation of all-included Weightings to produce an overall Weighted Average Rank in the Weighted Average Sheet / Master Table. I've highlighted the desired column yellow, but really I don't care where it is so long as it aligns with the static columns A - C in that table.

Examples of Weighted Average Ranks:
Banana - Banana is a Fruit (Type.) Looking in the PPL table, we see that Sandy Brown and Rob Camble are the only two whose rankings will be considered for Fruits, assuming their FnLn tables / sheets exist, which they both do. The weightings of 1.4 and 4.9 respectively are pulled into their tables, and multiplied against all Fruit types to get Weighted Ranks. They've each ranked Bananas, and so the weighted rank for Sandy is 1.56 while the weighted rank for Rob is 4.9. To calculated the combined weighted rank, the formula needs to sum all included Weighted Ranks and use that as the numerator, and then divide by a denominator which is the sum of the weightings used. In this example it would look like this: (1.56 + 4.9) / (1.4 + 4.9) = 6.46 / 6.3 = 1.025 which would be Banana's average weighted rank.

For Vegetables, only Jake and Sandy's ranks would be considered, and for Seeds, all three of Jake, Rob, and Sandy's ranks would be used.



I don't mind starting over or restructuring things if you determine that I'm going about this like a moron, but my experience with macros and VB is limited to me managing to screw everything up. I am time-pressed and cannot delve into learning it currently. I also have very, very minimal experience with Pivot Table or Power Query, but those seem like likely to break, so I'm at least open to those if they're the only option. My strong preference is for formulas though.

Any help is VERY much appreciated! Thanks!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,307
Office Version
  1. 365
Platform
  1. Windows
You could bring weighted ranks and weights into the one table, and calculate a weighted average along these lines:

EXAMPLE1.xlsx
ABCDEFGHIJKLMNOPQRSTUV
1SandyBrownSandyBrown        RobCambleRobCamble    JakeSmithJakeSmith
2PlantsRankTypeWeighted AvgWeightedRank1Weight1WeightedRank2Weight2WeightedRank3Weight3WeightedRank4Weight4WeightedRank5Weight5WeightedRank6Weight6WeightedRank7Weight7WeightedRank8Weight8WeightedRank9Weight9
3Banana1Fruit13.38.41.4        4.94.9    00
4Orange2Fruit27.32.81.4        24.54.9    00
5Lettuce3Vegetable33.1414.243.56        00    18.96.3
6Sunflower4Seed53.851.561.56        46.696.67    5.61.4
7Celery Seed5Seed34.817.81.56        20.016.67    71.4
8Walnuts6Nut0                  
9Broccoli7Vegetable72.5828.483.56        00    44.16.3
10Apple8Fruit43.44.21.4        39.24.9    00
11Pineapple9Fruit44.1          44.14.9      
Weighted Average
Cell Formulas
RangeFormula
E1:V1E1=INDEX(PPL[TableExists],COLUMNS($E1:F1)/2)
D3:D11D3=SUMPRODUCT(Master[@[WeightedRank1]:[Weight9]],--ISODD(COLUMN(Master[@[WeightedRank1]:[Weight9]])))
E3:E11,G3:G11,I3:I11,K3:K11,M3:M11,O3:O11,Q3:Q11,S3:S11,U3:U11E3=IFERROR(INDEX(INDIRECT(E$1&"[Weighted Rk]"), MATCH([@Plants],INDIRECT(E$1&"[Plants]"),0)),"")
F3:F11,H3:H11,J3:J11,L3:L11,N3:N11,P3:P11,R3:R11,T3:T11,V3:V11F3=IFERROR(INDEX(INDIRECT(E$1&"[Weighting]"), MATCH([@Plants],INDIRECT(E$1&"[Plants]"),0)),"")

But do your weighted rankings make sense? For example, is it meaningful that your weighted ranks are identical for:

a) an item ranked 1, with a weight of 10, and
b) an item ranked 10, with a weight of 1?
 

Forum statistics

Threads
1,144,578
Messages
5,725,095
Members
422,590
Latest member
Mikeyyy

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