JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,691
- Office Version
- 365
- Platform
- Windows
I am working on a project to calculate product ratings. It's a fairly complicated process so most of the calculations will be done in a macro. The project requires two "tables" that must have their columns aligned.
I am looking for the best way to keep two "tables" in sync -- or to be able to determine if they are out of alignment so it can return an error.
The illustration below is an example of 5 hypothetical electric vehicles with 4 properties: Price, Range, and two different external ratings (one on a 1-5 scale, the other on a 0-10 scale). The last 5 columns will not be part of the worksheet table. That work will be done in the macro. I have included them here for illustration purposes. The macro needs to access both the data table itself as well as the control data in the rows above the table. This example shows 2 control rows. The values in the Order row tell the macro whether these values are Hi-Lo (higher values are better) or Lo-Hi (lower values are better). The values in the Weight row tell the macro how much relative weight to assign to each property. In this example, Price is the only Lo-Hi property. The macro will use the property data to calculate z scores for each property, apply the weights, then convert the result to a 0-100 scale.
If I assign the weights as shown, I get the ratings shown.
I will need to add columns to the data table. When I do, I'll need to add synchronized columns to the control rows above the table. In my current implementation, I pass just the data table to the macro. It uses the address and dimensions of the table to access the control rows above. It then checks that the "headers" of the control rows match those of the data table. I have replicated the table headers in the row above the control rows so the macro can check that they are aligned. This works pretty well, but is there a better way?
I tried making the control rows into a table, too. It was simpler for the macro, but caused all kinds of problems with adding columns.
Here's the mini-sheet:
I am looking for the best way to keep two "tables" in sync -- or to be able to determine if they are out of alignment so it can return an error.
The illustration below is an example of 5 hypothetical electric vehicles with 4 properties: Price, Range, and two different external ratings (one on a 1-5 scale, the other on a 0-10 scale). The last 5 columns will not be part of the worksheet table. That work will be done in the macro. I have included them here for illustration purposes. The macro needs to access both the data table itself as well as the control data in the rows above the table. This example shows 2 control rows. The values in the Order row tell the macro whether these values are Hi-Lo (higher values are better) or Lo-Hi (lower values are better). The values in the Weight row tell the macro how much relative weight to assign to each property. In this example, Price is the only Lo-Hi property. The macro will use the property data to calculate z scores for each property, apply the weights, then convert the result to a 0-100 scale.
If I assign the weights as shown, I get the ratings shown.
I will need to add columns to the data table. When I do, I'll need to add synchronized columns to the control rows above the table. In my current implementation, I pass just the data table to the macro. It uses the address and dimensions of the table to access the control rows above. It then checks that the "headers" of the control rows match those of the data table. I have replicated the table headers in the row above the control rows so the macro can check that they are aligned. This works pretty well, but is there a better way?
I tried making the control rows into a table, too. It was simpler for the macro, but caused all kinds of problems with adding columns.
Here's the mini-sheet:
| Weighted Ratings Demo.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | |||
| 3 | Control Rows | |||||||||||||
| 4 | Product | Rating | Price | Range | Rtg1 | Rtg2 | ||||||||
| 5 | Order | Lo-Hi | Hi-Lo | Hi-Lo | Hi-Lo | |||||||||
| 6 | Weights | 2.00 | 3.00 | 1.00 | 1.50 | Calculations Done in the Macro | ||||||||
| 7 | Max | 4.599 | ||||||||||||
| 8 | Date Table | Min | -6.455 | |||||||||||
| 9 | Product | Rating | Price | Range | Rtg1 | Rtg2 | -- | Z Price | Z Range | Z Rtg1 | Z Rtg2 | Z Sum | ||
| 10 | D | 100 | $80,000 | 450 mi | 4.7 | 9.5 | -- | -1.43 | +0.80 | +1.14 | +2.62 | +4.60 | ||
| 11 | E | 88 | $51,000 | 460 mi | 4.6 | 8.1 | -- | +0.17 | +1.02 | +0.62 | -0.49 | +3.29 | ||
| 12 | A | 75 | $32,000 | 420 mi | 4.5 | 8.0 | -- | +1.21 | +0.13 | +0.10 | -0.71 | +1.87 | ||
| 13 | B | 29 | $62,000 | 390 mi | 4.4 | 8.2 | -- | -0.44 | -0.53 | -0.42 | -0.27 | -3.30 | ||
| 14 | C | 0 | $45,000 | 350 mi | 4.2 | 7.8 | -- | +0.50 | -1.42 | -1.46 | -1.15 | -6.45 | ||
| 15 | Std Dev | $18,125 | 45 mi | 0.2 | 0.7 | -- | 1.00 | +1.00 | +1.00 | +1.50 | +4.69 | |||
| 16 | Mean | $54,000 | 414 mi | 4.5 | 8.3 | -- | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | |||
MrExcel | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B4 | B4 | =TblTest[[#Headers],[Product]] |
| C4 | C4 | =TblTest[[#Headers],[Rating]] |
| D4 | D4 | =TblTest[[#Headers],[Price]] |
| E4 | E4 | =TblTest[[#Headers],[Range]] |
| F4 | F4 | =TblTest[[#Headers],[Rtg1]] |
| G4 | G4 | =TblTest[[#Headers],[Rtg2]] |
| M7 | M7 | =MAX(TblTest[Z Sum]) |
| M8 | M8 | =MIN(TblTest[Z Sum]) |
| I10:I14 | I10 | =([@Price]-D$16)/TblTest[[#Totals],[Price]] * ((D$5="Hi-Lo")*2-1) |
| J10:J14 | J10 | =([@Range]-E$16)/TblTest[[#Totals],[Range]] * ((E$5="Hi-Lo")*2-1) |
| K10:K14 | K10 | =F$6 * ([@Rtg1]-F$16)/TblTest[[#Totals],[Rtg1]] * ((F$5="Hi-Lo")*2-1) |
| L10:L14 | L10 | =G$6 * ([@Rtg2]-G$16)/TblTest[[#Totals],[Rtg2]] * ((G$5="Hi-Lo")*2-1) |
| M10:M14 | M10 | =D$6*[@[Z Price]] + E$6*[@[Z Range]] + F$6*[@[Z Rtg1]] + G$6*[@[Z Rtg2]] |
| I15 | I15 | =SUBTOTAL(107,[Z Price]) |
| J15 | J15 | =SUBTOTAL(107,[Z Range]) |
| K15 | K15 | =SUBTOTAL(107,[Z Rtg1]) |
| L15 | L15 | =SUBTOTAL(107,[Z Rtg2]) |
| M15 | M15 | =SUBTOTAL(107,[Z Sum]) |
| I16 | I16 | =AVERAGE(TblTest[Z Price]) |
| J16 | J16 | =AVERAGE(TblTest[Z Range]) |
| K16 | K16 | =AVERAGE(TblTest[Z Rtg1]) |
| L16 | L16 | =AVERAGE(TblTest[Z Rtg2]) |
| M16 | M16 | =AVERAGE(TblTest[Z Sum]) |
| C10:C14 | C10 | =100 * ([@[Z Sum]]-M$8) / (M$7-M$8) |
| D15 | D15 | =SUBTOTAL(107,[Price]) |
| E15 | E15 | =SUBTOTAL(107,[Range]) |
| F15 | F15 | =SUBTOTAL(107,[Rtg1]) |
| G15 | G15 | =SUBTOTAL(107,[Rtg2]) |
| D16 | D16 | =AVERAGE(TblTest[Price]) |
| E16 | E16 | =AVERAGE(TblTest[Range]) |
| F16 | F16 | =AVERAGE(TblTest[Rtg1]) |
| G16 | G16 | =AVERAGE(TblTest[Rtg2]) |
