diversification
New Member
- Joined
- Jun 24, 2020
- Messages
- 37
- Office Version
- 365
- Platform
- 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!
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!
