I could use help with a bit of a complex table addressing problem

[JenniferMurphy]

The minisheet below has a table that has some addressing problems.

• Column C has the names of some "products".
• Column D has what I am calling "composite ratings". They are the results of calculations done in the rest of the table.
• Columns E, N, and W are divider columns for illustration purposes only.
• Columns F-M contain ratings from various sources (R1 to R8).
  • R1 & R2 are 5-star ratings.
  • R3 is a 0-10 rating.
  • R4 is some sort of numerical rating.
  • R5 & R8 are rankings (1, 2, 3, ...).
  • R6 & R7 are another sort of numerical rating.
• The numbers above these columns in row 3 (F3:M3), contain relative weights to be assigned to these ratings.
• The text above these columns in row 2 (F2:M2), show whether the top ratings are the largest numbers (HiLo) or the smallest numbers (LoHi). This affects how the Z Scores are calculated.
• Columns O-V contain Z Scores of the values in Columns F-M. This is done by my ZScore UDF. This is where one of the addressing problems exists.
• Column X is the sum of the Z Scores with the weights applied. This is the other addressing problem.

All those absolute (or semi-absolute (F$14)) addresses are a problem. If I need to add, delete, or reorder any of the rating columns, I have to make sure that the helper rows above the table and the Z Score columns stay aligned.

One solution would be to move the helper rows inside the table (at the top). But that is a problem if I want to sort the table on the composite ratings.

Another solution is move the Z-Score columns and the Z Sum column to a UDF. Then I only have to keep the helper rows in sync, which is much easier. That is my current plan, but before I do that, I thought I would check here to see if there is a better way. And even if I do that, how best to keep those helper rows in sync.

And then there is the table shortcoming of only allowing 1 total row. I need both the mean and the std dev. One of them has to be outside the table and not accessible with table syntax. I suppose I could use Offset to get from the mean to the std dev. And then I could use Offset to get from the headers to the helper rows. I think I'll try that next.

I'd appreciate any other suggestions.

Thanks

Access Tutorial Ratings.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
2		Order		HiLo	HiLo	HiLo	HiLo	LoHi	HiLo	HiLo	LoHi
3		Weight		2	2	2	1	1	1	1	1
4	Product	Composite Rating (0-100)	P Start	R1	R2	R3	R4	R5	R6	R7	R8	Z Start	Z R1	Z R2	Z R3	Z R4	Z R5	Z R6	Z R7	Z R8	Z End	Z Sum
5	A	100		4.4	4.6	9.1	638.91	4	36.1	63.9	2		+0.08	+0.20	+1.41	+0.03	+0.13	+0.21	+1.56	+0.79		+6.09
6	B	89		4.6	4.7	9.0					12		+1.29	+0.98	+0.71					-1.51		+4.44
7	C	77		4.4	4.6	8.8	1039.46	2	55.6	47.1	1		+0.08	+0.20	-0.71	+0.94	+0.64	+1.78	-0.95	+1.02		+2.55
8	D	59		4.5	4.6	8.8	429.83	5	26.4	56.0	4		+0.69	+0.20	-0.71	-0.44	-0.13	-0.57	+0.38	+0.33		-0.08
9	E	57		4.5	4.7	9.0	109.95	12	31.2	49.4	11		+0.69	+0.98	+0.71	-1.17	-1.91	-0.18	-0.61	-1.28		-0.41
10	F	51		4.4	4.6	8.7	807.65	3	33.0	50.9	3		+0.08	+0.20	-1.41	+0.42	+0.38	-0.04	-0.38	+0.56		-1.35
11	G	45		4.2	4.5	8.9	1240.37	1	18.6		5		-1.14	-0.59	=0.00	+1.40	+0.89	-1.20		+0.10		-2.26
12	H	0		4.1	4.3		100.63						-1.75	-2.15		-1.19						-8.98
13	Averages	59.6		4.4	4.6	8.9	623.8	4.5	33.5	53.5	5.4		0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0		0.0
14	Std Devs	30.83		0.16	0.13	0.14	440.14	3.94	12.43	6.69	4.35		1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00		4.65
15	Max																					+6.09
16	Min																					-8.98
Mr Excel

Cell Formulas
Range	Formula
O5:O12	O5	=ZScore([@R1],Table13[[#Totals],[R1]],F$14,F$2)
P5:P12	P5	=ZScore([@R2],Table13[[#Totals],[R2]],G$14,G$2)
Q5:Q12	Q5	=ZScore([@R3],Table13[[#Totals],[R3]],H$14,H$2)
R5:R12	R5	=ZScore([@R4],Table13[[#Totals],[R4]],I$14,I$2)
S5:S12	S5	=ZScore([@R5],Table13[[#Totals],[R5]],J$14,J$2)
T5:T12	T5	=ZScore([@R6],Table13[[#Totals],[R6]],K$14,K$2)
U5:U12	U5	=ZScore([@R7],Table13[[#Totals],[R7]],L$14,L$2)
V5:V12	V5	=ZScore([@R8],Table13[[#Totals],[R8]],M$14,M$2)
O13	O13	=SUBTOTAL(101,[Z R1])
P13	P13	=SUBTOTAL(101,[Z R2])
Q13	Q13	=SUBTOTAL(101,[Z R3])
R13	R13	=SUBTOTAL(101,[Z R4])
S13	S13	=SUBTOTAL(101,[Z R5])
T13	T13	=SUBTOTAL(101,[Z R6])
U13	U13	=SUBTOTAL(101,[Z R7])
V13	V13	=SUBTOTAL(101,[Z R8])
O14	O14	=STDEV.S(Table13[Z R1])
P14	P14	=STDEV.S(Table13[Z R2])
Q14	Q14	=STDEV.S(Table13[Z R3])
R14	R14	=STDEV.S(Table13[Z R4])
S14	S14	=STDEV.S(Table13[Z R5])
T14	T14	=STDEV.S(Table13[Z R6])
U14	U14	=STDEV.S(Table13[Z R7])
V14	V14	=STDEV.S(Table13[Z R8])
F13	F13	=SUBTOTAL(101,[R1])
G13	G13	=SUBTOTAL(101,[R2])
H13	H13	=SUBTOTAL(101,[R3])
I13	I13	=SUBTOTAL(101,[R4])
J13	J13	=SUBTOTAL(101,[R5])
K13	K13	=SUBTOTAL(101,[R6])
L13	L13	=SUBTOTAL(101,[R7])
M13	M13	=SUBTOTAL(101,[R8])
F14	F14	=STDEV.S(Table13[R1])
G14	G14	=STDEV.S(Table13[R2])
H14	H14	=STDEV.S(Table13[R3])
I14	I14	=STDEV.S(Table13[R4])
J14	J14	=STDEV.S(Table13[R5])
K14	K14	=STDEV.S(Table13[R6])
L14	L14	=STDEV.S(Table13[R7])
M14	M14	=STDEV.S(Table13[R8])
D5:D12	D5	=ScaleRatings([@[Z Sum]],ZSumMax,ZSumMin)
D13	D13	=SUBTOTAL(101,[Composite Rating (0-100)])
D14	D14	=STDEV.S(Table13[Composite Rating (0-100)])
X5:X12	X5	=SUMPRODUCT(Table13[@[Z R1]:[Z R8]],F$3:M$3)
X13	X13	=SUBTOTAL(101,[Z Sum])
X14	X14	=STDEV.S(Table13[Z Sum])
X15	X15	=MAX(Table13[Z Sum])
X16	X16	=MIN(Table13[Z Sum])

Named Ranges
Name	Refers To	Cells
'Mr Excel'!ZSumMax	='Mr Excel'!$X$15	D5:D12
'Mr Excel'!ZSumMin	='Mr Excel'!$X$16	D5:D12

 

[JenniferMurphy]

I took my own suggestions. I used Offset addressing to get to the std dev's and the Orders (HiLo/LoHi). I then assigned a name to the range of weights, also using offset addressing.

It seems to work.

Comments?

Access Tutorial Ratings.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
2		Order		HiLo	HiLo	HiLo	HiLo	LoHi	HiLo	HiLo	LoHi
3		Weight		2	2	2	1	1	1	1	1
4	Product	Composite Rating (0-100)	P Start	R1	R2	R3	R4	R5	R6	R7	R8	Z Start	Z R1	Z R2	Z R3	Z R4	Z R5	Z R6	Z R7	Z R8	Z End	Z Sum
5	A	100		4.4	4.6	9.1	638.91	4	36.1	63.9	2		+0.08	+0.20	+1.41	+0.03	+0.13	+0.21	+1.56	+0.79		+6.09
6	B	89		4.6	4.7	9.0					12		+1.29	+0.98	+0.71					-1.51		+4.44
7	C	77		4.4	4.6	8.8	1039.46	2	55.6	47.1	1		+0.08	+0.20	-0.71	+0.94	+0.64	+1.78	-0.95	+1.02		+2.55
8	D	59		4.5	4.6	8.8	429.83	5	26.4	56.0	4		+0.69	+0.20	-0.71	-0.44	-0.13	-0.57	+0.38	+0.33		-0.08
9	E	57		4.5	4.7	9.0	109.95	12	31.2	49.4	11		+0.69	+0.98	+0.71	-1.17	-1.91	-0.18	-0.61	-1.28		-0.41
10	F	51		4.4	4.6	8.7	807.65	3	33.0	50.9	3		+0.08	+0.20	-1.41	+0.42	+0.38	-0.04	-0.38	+0.56		-1.35
11	G	45		4.2	4.5	8.9	1240.37	1	18.6		5		-1.14	-0.59	=0.00	+1.40	+0.89	-1.20		+0.10		-2.26
12	H	0		4.1	4.3		100.63						-1.75	-2.15		-1.19						-8.98
13	Averages	59.6		4.4	4.6	8.9	623.8	4.5	33.5	53.5	5.4		0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0		0.0
14	Std Devs	30.83		0.16	0.13	0.14	440.14	3.94	12.43	6.69	4.35		1.00	1.00	1.00	1.00	1.00	1.00	1.00	1.00		4.65
15	Max																					+6.09
16	Min																					-8.98
Mr Excel

Cell Formulas
Range	Formula
O5:O12	O5	=ZScore([@R1],Table13[[#Totals],[R1]],OFFSET(Table13[[#Totals],[R1]],1,0),OFFSET(Table13[[#Headers],[R1]],-2,0))
P5:P12	P5	=ZScore([@R2],Table13[[#Totals],[R2]],OFFSET(Table13[[#Totals],[R2]],1,0),OFFSET(Table13[[#Headers],[R2]],-2,0))
Q5:Q12	Q5	=ZScore([@R3],Table13[[#Totals],[R3]],OFFSET(Table13[[#Totals],[R3]],1,0),OFFSET(Table13[[#Headers],[R3]],-2,0))
R5:R12	R5	=ZScore([@R4],Table13[[#Totals],[R4]],OFFSET(Table13[[#Totals],[R4]],1,0),OFFSET(Table13[[#Headers],[R4]],-2,0))
S5:S12	S5	=ZScore([@R5],Table13[[#Totals],[R5]],OFFSET(Table13[[#Totals],[R5]],1,0),OFFSET(Table13[[#Headers],[R5]],-2,0))
T5:T12	T5	=ZScore([@R6],Table13[[#Totals],[R6]],OFFSET(Table13[[#Totals],[R6]],1,0),OFFSET(Table13[[#Headers],[R6]],-2,0))
U5:U12	U5	=ZScore([@R7],Table13[[#Totals],[R7]],OFFSET(Table13[[#Totals],[R7]],1,0),OFFSET(Table13[[#Headers],[R7]],-2,0))
V5:V12	V5	=ZScore([@R8],Table13[[#Totals],[R8]],OFFSET(Table13[[#Totals],[R8]],1,0),OFFSET(Table13[[#Headers],[R8]],-2,0))
O13	O13	=SUBTOTAL(101,[Z R1])
P13	P13	=SUBTOTAL(101,[Z R2])
Q13	Q13	=SUBTOTAL(101,[Z R3])
R13	R13	=SUBTOTAL(101,[Z R4])
S13	S13	=SUBTOTAL(101,[Z R5])
T13	T13	=SUBTOTAL(101,[Z R6])
U13	U13	=SUBTOTAL(101,[Z R7])
V13	V13	=SUBTOTAL(101,[Z R8])
O14	O14	=STDEV.S(Table13[Z R1])
P14	P14	=STDEV.S(Table13[Z R2])
Q14	Q14	=STDEV.S(Table13[Z R3])
R14	R14	=STDEV.S(Table13[Z R4])
S14	S14	=STDEV.S(Table13[Z R5])
T14	T14	=STDEV.S(Table13[Z R6])
U14	U14	=STDEV.S(Table13[Z R7])
V14	V14	=STDEV.S(Table13[Z R8])
F13	F13	=SUBTOTAL(101,[R1])
G13	G13	=SUBTOTAL(101,[R2])
H13	H13	=SUBTOTAL(101,[R3])
I13	I13	=SUBTOTAL(101,[R4])
J13	J13	=SUBTOTAL(101,[R5])
K13	K13	=SUBTOTAL(101,[R6])
L13	L13	=SUBTOTAL(101,[R7])
M13	M13	=SUBTOTAL(101,[R8])
F14	F14	=STDEV.S(Table13[R1])
G14	G14	=STDEV.S(Table13[R2])
H14	H14	=STDEV.S(Table13[R3])
I14	I14	=STDEV.S(Table13[R4])
J14	J14	=STDEV.S(Table13[R5])
K14	K14	=STDEV.S(Table13[R6])
L14	L14	=STDEV.S(Table13[R7])
M14	M14	=STDEV.S(Table13[R8])
D5:D12	D5	=ScaleRatings([@[Z Sum]],ZSumMax,ZSumMin)
D13	D13	=SUBTOTAL(101,[Composite Rating (0-100)])
D14	D14	=STDEV.S(Table13[Composite Rating (0-100)])
X5:X12	X5	=SUMPRODUCT(Table13[@[Z R1]:[Z R8]],Weights)
X13	X13	=SUBTOTAL(101,[Z Sum])
X14	X14	=STDEV.S(Table13[Z Sum])
X15	X15	=MAX(Table13[Z Sum])
X16	X16	=MIN(Table13[Z Sum])

Named Ranges
Name	Refers To	Cells
'Mr Excel'!Weights	=OFFSET('Mr Excel'!WtBeg,0,1):OFFSET('Mr Excel'!WtEnd,0,-1)	X5:X12
'Mr Excel'!WtBeg	='Mr Excel'!$E$3	X5:X12
'Mr Excel'!WtEnd	='Mr Excel'!$N$3	X5:X12
'Mr Excel'!ZSumMax	='Mr Excel'!$X$15	D5:D12
'Mr Excel'!ZSumMin	='Mr Excel'!$X$16	D5:D12