How to manage data data outside a table?

[JenniferMurphy]

The mini-sheet below contains a table with sample ratings for several "products". I'm working on an algorithm for calculating a weighted average rating based on a number of proerties. In this example, the properties are ratings from 3 different sources (Rtg 1, Rtg2, and Rtg 3). Rows 4 & 5 above the table, contain the maximum and minimum values for each rating. These allow me to calculate the normalized ratings. Is there a good way to keep these values in the right columns if I insert a column somewhere.

Weighted Ratings.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
4					Max Value	5			10			100
5					Min Value	1			0			0
6					Weight	1			1			1
7
8	Item	0-1 Sum	0-1 Rank	Z Sum	Z Rank	Rtg 1	0-1 1	Z 1	Rtg 2	0-1 2	Z 2	Rtg 3	0-1 3	Z 3
9	A	1.74	2	-0.26	4	4.8	0.95	1.37	7	0.7	-0.61	79	0.79	-1.02
10	D	1.74	3	1.29	1	4.1	0.78	-1.15	9	0.9	0.92	96	0.96	1.53
11	E	1.69	4	-1.81	5	4.5	0.88	0.29	6	0.6	-1.38	81	0.81	-0.72
12	B	1.65	5	0.01	3	4.2	0.80	-0.79	9	0.9	0.92	85	0.85	-0.12
13	C	1.76	1	0.77	2	4.5	0.88	0.29	8	0.8	0.15	88	0.88	0.33
14	Std Dev					0.28			1.30			6.69
15	Mean					4.42			7.80			85.80
Z Scores

Cell Formulas
Range	Formula
D9:D13	D9	=[@[0-1 1]]+[@[0-1 3]]
E9:E13	E9	=RANK.AVG([@[0-1 Sum]],[0-1 Sum])
F9:F13	F9	=[@[Z 1]]+[@[Z 2]]+[@[Z 3]]
G9:G13	G9	=RANK.AVG([@[Z Sum]],[Z Sum])
I9:I13	I9	=([@[Rtg 1]]-H$5)/(H$4-H$5)
J9:J13	J9	=([@[Rtg 1]]-H$15)/Table2[[#Totals],[Rtg 1]]
L9:L13	L9	=([@[Rtg 2]]-K$5)/(K$4-K$5)
M9:M13	M9	=([@[Rtg 2]]-K$15)/Table2[[#Totals],[Rtg 2]]
O9:O13	O9	=([@[Rtg 3]]-N$5)/(N$4-N$5)
P9:P13	P9	=([@[Rtg 3]]-N$15)/Table2[[#Totals],[Rtg 3]]
H14	H14	=SUBTOTAL(107,[Rtg 1])
H15	H15	=AVERAGE(Table2[Rtg 1])
K14	K14	=SUBTOTAL(107,[Rtg 2])
K15	K15	=AVERAGE(Table2[Rtg 2])
N14	N14	=SUBTOTAL(107,[Rtg 3])
N15	N15	=AVERAGE(Table2[Rtg 3])

Thanks

 

[JenniferMurphy]

Perhaps I should have chosen a better title. What I am trying to do it connect some data outside a table with corresponding data inside the table. Here's a better example than the one above. This table contains a list of password managers. For each one, I have columns for the date, the product name, the rating, the price, and columns for ratings by 4 different companies. There are several more to add, plus I want to add columns for features that I care about. The Rating column will be a call to the UDF I am writing. It will examine the entire table and calculate an overall rating. I am happy to discuss the algorithm in another thread if anyone is interested.

Above the table in yellow, are 2 rows with information that the algorithm needs to do its work. The Weights row tells the algorithm how much (relative) weight to give to each data column. The Codes row tells the algorithm what kind of data is in that column. For example, the CyberNews column has 5 star ratings; the PC Mag column has ranked the products from 1 to 10.

Password Managers.xlsx
	B	C	D	E	F	G	H	I
2			Weights	1	1.5	1.2	0.8	1.6
3			Codes	$	5-1	1-10	1-3	1-13
4	Date	Product	Rating	Price (1 year)	CyberNews	PC Mag	Wired	Tom's Guide
5	9/22/22	1Password		$36	4.0	5	1	2
6	9/22/22	Avira		$32	3.8
7	9/23/22	Bitwarden		$60		3	1	5
8	9/23/22	Blur						9
9	9/22/22	Dashlane		$60	4.2	9	1	4
10	9/22/22	Enpass		$24	3.7		3	11
11	9/23/22	KeePass						10
12	9/22/22	Keeper		$35	4.6	1	3	3
13	9/22/22	LastPass		$36	3.9	4	3	1
14	9/23/22	LogMeOnce		$39		6
15	9/23/22	Myki		$48				7
16	9/22/22	Nordpass		$36	4.9	10	2	6
17	9/22/22	Norton		$85	3.9
18	9/22/22	PassWarden		$40	4.0
19	9/23/22	Password Boss		??		8
20	9/22/22	Roboform		$14	4.4	7	3	8
21	9/23/22	True Key						13
22	9/23/22	Zoho		$54		2		12
Sheet1

I have 2 problems:

1. If I am not careful when inserting, deleting, or moving table columns, the codes above may not stay connected.
2. I can pass the entire table to the UDF using the table name and that range will expand or contract as data is added or deleted. But how to pass the location of the 2 code rows aboive the table? Can the UDF query the absolute address of each cell in the table and then use the offset function to get to the code data above?

Let me know if I haven't explained this well enough or if there are any questions.

Thanks for any help.

 

[RoryA]

I'd suggest putting that data into its own lookup table.

 

[JenniferMurphy]

I'd suggest putting that data into its own lookup table.

I'm not sure what you mean by "lookup table". It must not be a "table" table. I just tried selecting those 2 rows and clicking on Format as Table. With two tables in the same columns, Excel will not allow me to insert columns from the column letter. And if I do it from the table column, it inly affects that table.

If it is not a table table, then how do I name it so I can pass it to the UDF? Table names expand with the table. Not so with named ranges. I have named the cells before and after the range and then defined a named range using offset, but this does not guarantee that the columns will remain "connected" (lined up) with thedata in the main table.

Thanks

 

[RoryA]

I mean a three column table located elsewhere. First column is the name of the column to which it applies, second column is the Weight and third column is the Code.

 

[JenniferMurphy]

I mean a three column table located elsewhere. First column is the name of the column to which it applies, second column is the Weight and third column is the Code.

I see. I think this is more of a hassle than just putting it where it is and being careful. Having it lined up with the corresponding values makes it easy to see and manage. The only options for "elsewhere" are (a) a different sheet and (b) different columns in the same sheet (way off to the right). Neither has the clear visual alignment.

I guess I'll use my usual offset naming scheme and be careful. I might replicate the table headers so I can more easily tell if something gets out of whack.

Password Managers.xlsx
	B	C	D	E	F	G	H	I
2				Price (1 year)	CyberNews	PC Mag	Wired	Tom's Guide
3			Weights	1	1.5	1.2	0.8	1.6
4			Codes	$	5-1	1-10	1-3	1-5
5
6	Date	Product	Rating	Price (1 year)	CyberNews	PC Mag	Wired	Tom's Guide
7	9/22/22	1Password		$36	4.0	5	1	2
8	9/22/22	Avira		$32	3.8
9	9/23/22	Bitwarden		$60		3	1	5
10	9/23/22	Blur						9
11	9/22/22	Dashlane		$60	4.2	9	1	4
12	9/22/22	Enpass		$24	3.7		3	11
13	9/23/22	KeePass						10
14	9/22/22	Keeper		$35	4.6	1	3	3
15	9/22/22	LastPass		$36	3.9	4	3	1
16	9/23/22	LogMeOnce		$39		6
17	9/23/22	Myki		$48				7
18	9/22/22	Nordpass		$36	4.9	10	2	6
19	9/22/22	Norton		$85	3.9
20	9/22/22	PassWarden		$40	4.0
21	9/23/22	Password Boss		??		8
22	9/22/22	Roboform		$14	4.4	7	3	8
23	9/23/22	True Key						13
24	9/23/22	Zoho		$54		2		12
Sheet1

I should probably move this application to a database.

Thanks