Automated Indices

J

Jwtully

New Member
Joined
Oct 18, 2017
Messages
1
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.
[TABLE="width: 0, align: center"]
<tbody>[TR]
[TD]Variable Index 1
[/TD]
[TD]Variable Index 2
[/TD]
[/TR]
[TR]
[TD]Tool 1
[/TD]
[TD]Tool 1
[/TD]
[/TR]
[TR]
[TD]Tool 2
[/TD]
[TD]Tool 3
[/TD]
[/TR]
[TR]
[TD]Tool 4
[/TD]
[TD]Tool 5
[/TD]
[/TR]
</tbody>[/TABLE]


Below is the current dataset structure.
[TABLE="width: 0, align: center"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Respondent ID
[/TD]
[TD]Current Role
[/TD]
[TD]Client
[/TD]
[TD]Variable Index 1
[/TD]
[TD]Variable Index 2
[/TD]
[TD]Tool 1
[/TD]
[TD]Tool 2
[/TD]
[TD]Tool 3
[/TD]
[TD]Tool 4
[/TD]
[TD]Tool 5
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]6390901863
[/TD]
[TD]Manager
[/TD]
[TD]Client 1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]6393811019
[/TD]
[TD]Director
[/TD]
[TD]Client 1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]6399578106
[/TD]
[TD]Analyst
[/TD]
[TD]Client 1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]6401731956
[/TD]
[TD]Manager
[/TD]
[TD]Client 1
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]6400323399
[/TD]
[TD]Sr. Analyst
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]6406122762
[/TD]
[TD]Sr. Analyst
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]6406201649
[/TD]
[TD]Manager
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]6390869805
[/TD]
[TD]Analyst
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]6406150883
[/TD]
[TD]Director
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]6408579156
[/TD]
[TD]Director
[/TD]
[TD]Client 2
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]TOTAL
[/TD]
[TD]TOTAL
[/TD]
[TD]TOTAL
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Average
[/TD]
[TD]Average
[/TD]
[TD]Average
[/TD]
[TD]Average
[/TD]
[TD]Average
[/TD]
[/TR]
</tbody>[/TABLE]

*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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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!).

ABCDEFGHIJ
Variable Index 1Variable Index 2
Tool 1Tool 1
Tool 2Tool 3
Tool 4Tool 5
Respondent IDCurrent RoleClientVariable Index 1Variable Index 2Tool 1Tool 2Tool 3Tool 4Tool 5
6390901863ManagerClient 1
6393811019DirectorClient 1
6399578106AnalystClient 1
6401731956ManagerClient 1
6400323399Sr. AnalystClient 2
6406122762Sr. AnalystClient 2
6406201649ManagerClient 2
6390869805AnalystClient 2
6406150883DirectorClient 2
6408579156DirectorClient 2
Formula construction testingformulapointing
cell d7Sum Respondent Tool Score
Sum Current Role Tool Score
Sum Client Tool Score
Sum Total Tool Score
single cell

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]96.43[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]100.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]88.04[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]88.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]90.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]100.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]64.29[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]54.55[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]120.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]133.33[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]60.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]66.67[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]51.43[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]45.45[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]108.00[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]100.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]62.61[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]64.00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]46.96[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]48.00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]23[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]21[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]63[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]63[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]96.43[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C6E0B4]#C6E0B4[/URL] , align: right"]96.43[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet34​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E21[/TH]
[TD="align: left"]=(F7+G7+I7)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E22[/TH]
[TD="align: left"]=(F7+G7+I7+F10+G10+I10+F13+G13+I13)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E23[/TH]
[TD="align: left"]=(F7+F8+F9+F10+G7+G8+G9+G10+I7+I8+I9+I10)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E24[/TH]
[TD="align: left"]=(F17+G17+I17)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E25[/TH]
[TD="align: left"]=((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[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]{=((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}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D21[/TH]
[TD="align: left"]{=SUM(MMULT(F7:J16*(A7:A16=A7),TRANSPOSE(--(F6:J6=A2:A4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D22[/TH]
[TD="align: left"]{=SUM(MMULT(F7:J16*(B7:B16=B7),TRANSPOSE(--(F6:J6=A2:A4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D23[/TH]
[TD="align: left"]{=SUM(MMULT(F7:J16*(C7:C16=C7),TRANSPOSE(--(F6:J6=A2:A4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D24[/TH]
[TD="align: left"]{=SUM(MMULT(F7:J16,TRANSPOSE(--(F6:J6=A2:A4))))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D25[/TH]
[TD="align: left"]{=((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}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
YTD and Period Averages? Possible averageif with index/match?
Replies
2
Views
57
reacon84
R
O
  • Solved
How to calculate annual average growth (i.e., price appreciation) rate for an investment portfolio.
Replies
3
Views
144
kvsrinivasamurthy
K
M
  • Solved
If a value of a range in column B equals a range of values in column J, then multiply F by K and fill Column G
Replies
5
Views
333
smozgur
smozgur
H
  • Solved
Index and match formula that ignores dynamic value
2
Replies
14
Views
937
FormR
FormR
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
392
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,224,828
Messages
6,181,215
Members
453,024
Latest member
Wingit77

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top