Automated Indices

[Jwtully]

Hello All,

I’ve recently been given the task to create a pseudo-automated system that can have tools in a list output a user experience index based on a data set.

As of right now, I’ve been grabbing the data manually and doing the math through a number of SUMIFS and AVERAGEIFS. You can imagine, this system is time intensive and not scalable to any degree. Especially with future request indices reaching nearly 25+ tools.

My current assumption is to use an Index with a small min + 1 row equations then pile some my SUMIFs and AVERAGEIFs on top, but I’m thinking that’s to heavy.

Below is the dummy dataset as an example.

The Index equation is:

(Sum Respondent Tool Score / Sum Current Role Tool Score)	X 100
(Sum Client Tool Score / Sum Total Tool Score)

<tbody>
</tbody>

Below is the variable list I’d like the array to grab from.

Variable Index 1	Variable Index 2
Tool 1	Tool 1
Tool 2	Tool 3
Tool 4	Tool 5

<tbody>
</tbody>

Below is the current dataset structure.

	A	B	C	D	E	F	G	H	I	J
1	Respondent ID	Current Role	Client	Variable Index 1	Variable Index 2	Tool 1	Tool 2	Tool 3	Tool 4	Tool 5
2	6390901863	Manager	Client 1			4	2	3	3	4
3	6393811019	Director	Client 1			4	2	3	3	4
4	6399578106	Analyst	Client 1			2	1	2	1	2
5	6401731956	Manager	Client 1			2	2	2	2	2
6	6400323399	Sr. Analyst	Client 2			2	2	2	2	2
7	6406122762	Sr. Analyst	Client 2			1	1	1	1	1
8	6406201649	Manager	Client 2			2	2	1	2	2
9	6390869805	Analyst	Client 2			2	2	2	2	2
10	6406150883	Director	Client 2			4	2	2	2	2
11	6408579156	Director	Client 2			2	2	2	2	2
12	TOTAL	TOTAL	TOTAL			Average	Average	Average	Average	Average

<tbody>
</tbody>

*values are respondent’s experience in that tool.

Any help is appreciate, and if I’ve left anything out please let me know.

Thanks for your time!

Jwtully

 

[DRSteele]

Welcome to the forum.

Attempting this task with INDEX will drive you insane. The whole thing becomes clear and easy if the data is treated as a matrix.

Copy my D7 to D7:E16. You can expand the whole thing to 25 Tools and as many Variable Indexes as you care to by adding columns between the Tools and adding Variable Index 3 onwards. Make sure to deal carefully with the absolute and various cell references (where I hope I got it right!).

	A	B	C	D	E	F	G	H	I	J
1	Variable Index 1	Variable Index 2
2	Tool 1	Tool 1
3	Tool 2	Tool 3
4	Tool 4	Tool 5
5
6	Respondent ID	Current Role	Client	Variable Index 1	Variable Index 2	Tool 1	Tool 2	Tool 3	Tool 4	Tool 5
7	6390901863	Manager	Client 1	96.43	100.00	4	2	3	3	4
8	6393811019	Director	Client 1	88.04	88.00	4	2	3	3	4
9	6399578106	Analyst	Client 1	90.00	100.00	2	1	2	1	2
10	6401731956	Manager	Client 1	64.29	54.55	2	2	2	2	2
11	6400323399	Sr. Analyst	Client 2	120.00	133.33	2	2	2	2	2
12	6406122762	Sr. Analyst	Client 2	60.00	66.67	1	1	1	1	1
13	6406201649	Manager	Client 2	51.43	45.45	2	2	1	2	2
14	6390869805	Analyst	Client 2	108.00	100.00	2	2	2	2	2
15	6406150883	Director	Client 2	62.61	64.00	4	2	2	2	2
16	6408579156	Director	Client 2	46.96	48.00	2	2	2	2	2
17						25	18	20	20	23
18
19
20	Formula construction testing			formula	pointing
21	cell d7	Sum Respondent Tool Score		9	9
22		Sum Current Role Tool Score		21	21
23		Sum Client Tool Score		28	28
24		Sum Total Tool Score		63	63
25		single cell		96.43	96.43

<tbody>
</tbody>

Sheet34​

Worksheet Formulas Cell Formula E21 =(F7+G7+I7) E22 =(F7+G7+I7+F10+G10+I10+F13+G13+I13) E23 =(F7+F8+F9+F10+G7+G8+G9+G10+I7+I8+I9+I10) E24 =(F17+G17+I17) E25 =((F7+G7+I7)/(F7+G7+I7+F10+G10+I10+F13+G13+I13))/((F7+F8+F9+F10+G7+G8+G9+G10+I7+I8+I9+I10)/(F17+G17+I17))*100 <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D7 {=((SUM(MMULT($F$7:$J$16*($A$7:$A$16=$A7),TRANSPOSE(--($F$6:$J$6=A$2:A$4)))))/(SUM(MMULT($F$7:$J$16*($B$7:$B$16=$B7),TRANSPOSE(--($F$6:$J$6=A$2:A$4))))))/((SUM(MMULT($F$7:$J$16*($C$7:$C$16=$C7),TRANSPOSE(--($F$6:$J$6=A$2:A$4)))))/SUM(MMULT($F$7:$J$16,TRANSPOSE(--($F$6:$J$6=A$2:A$4)))))*100} D21 {=SUM(MMULT(F7:J16*(A7:A16=A7),TRANSPOSE(--(F6:J6=A2:A4))))} D22 {=SUM(MMULT(F7:J16*(B7:B16=B7),TRANSPOSE(--(F6:J6=A2:A4))))} D23 {=SUM(MMULT(F7:J16*(C7:C16=C7),TRANSPOSE(--(F6:J6=A2:A4))))} D24 {=SUM(MMULT(F7:J16,TRANSPOSE(--(F6:J6=A2:A4))))} D25 {=((SUM(MMULT(F7:J16*(A7:A16=A7),TRANSPOSE(--(F6:J6=A2:A4)))))/(SUM(MMULT(F7:J16*(B7:B16=B7),TRANSPOSE(--(F6:J6=A2:A4))))))/((SUM(MMULT(F7:J16*(C7:C16=C7),TRANSPOSE(--(F6:J6=A2:A4)))))/SUM(MMULT(F7:J16,TRANSPOSE(--(F6:J6=A2:A4)))))*100} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>