Convert a set of non-contiguous values into an array for use in SUMPRODUCT

[keylimesoda]

Goal is to get a set of non-contiguous values from a row into an array so it can be combined (e.g. SUMPRODUCT) with other arrays. The values in the array should be selected from the current row based on table column name.

I'd thought I could do something like:

=SUMPRODUCT(

G5:G7,​

INDEX(​

([@speedRating],[@strengthRating],[@agilityRating]),​

1,​

0​

)
​

)

However, the INDEX function simply evaluates to the value of @speedRating, rather than a full array.

How do I get my set of non-contiguous values from my row into an array for further manipulation?

 

[XOR LX]

Hi,

You could use CHOOSE, i.e.:

CHOOSE({1;2;3},[@speedRating],[@strengthRating],[@agilityRating])

which will produce a vertical vector comprising those 3 values. For the equivalent horizontal vector use:

CHOOSE({1,2,3},[@speedRating],[@strengthRating],[@agilityRating])

Note that, if you are using a non-English language version of Excel, these array constant separators may require amending.

Regards

 

[keylimesoda]

It looks like that should work!

However, I have 46 different ratings (not the 3 used in my example). Is there a way to get around writing out each of the numbers 1-46 in the first set of the CHOOSE function?

 

[XOR LX]

Is that part dynamic? If not, here it is for you!

Vertical:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46}

Horizontal:

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46}

Regards

 

[keylimesoda]

I was thinking to save space in the function, but I am amazed by your kindness to type those all out for me. Thank you

 

[keylimesoda]

Okay, so here is the whole ugly function:

=SUMPRODUCT(INDEX(Weights!$C$4:Weights!$X$47, 0, MATCH(C2, Weights!$C$4:Weights!$X$4,0)), CHOOSE({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43},([@speedRating],[@accelRating],[@strengthRating],[@agilityRating],[@accelRating],[@awareRating],[@truckRating],[@elusiveRating],[@bCVRating],[@stiffArmRating],[@spinMoveRating],[@jukeMoveRating],[@carryRating],[@catchRating],[@routeRunRating],[@cITRating],[@specCatchRating],[@releaseRating],[@jumpRating],[@throwPowerRating],[@throwAccShortRating],[@throwAccMidRating],[@throwAccDeepRating],[@throwOnRunRating],[@playActionRating],[@tackleRating],[@hitPowerRating],[@powerMovesRating],[@finesseMovesRating],[@blockShedRating],[@pursuitRating],[@playRecRating],[@manCoverRating],[@zoneCoverRating],[@pressRating],[@kickPowerRating],[@kickAccRating],[@impactBlockRating],[@runBlockRating],[@passBlockRating],[@kickRetRating],[@toughRating],[@staminaRating],[@injuryRating])))

Worksheet is here: https://1drv.ms/x/s!Ah8EhteTsIhUiPks9FSrI5soPSB90w

I'm still getting a #VALUE error when trying to run this function. In evaluation, it looks like the individual values in our constructed array are evaluating to #VALUE for some reason. Any ideas why eval in our constructed array may be failing?

 

[XOR LX]

Actually if you know a bit of Excel you don't have to 'type' them out at all!

Here're the dynamic (non-volatile) versions if you're interested (which may require committing with CTRL+SHIFT+ENTER, depending upon in which functions they are employed):

Vertical:

ROW(INDEX(A:A,1):INDEX(A:A,46))

Horizontal:

COLUMN(INDEX(1:1,1):INDEX(1:1,46))

Regards

 

[XOR LX]

Okay, so here is the whole ugly function:

=SUMPRODUCT(INDEX(Weights!$C$4:Weights!$X$47, 0, MATCH(C2, Weights!$C$4:Weights!$X$4,0)), CHOOSE({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43},([@speedRating],[@accelRating],[@strengthRating],[@agilityRating],[@accelRating],[@awareRating],[@truckRating],[@elusiveRating],[@bCVRating],[@stiffArmRating],[@spinMoveRating],[@jukeMoveRating],[@carryRating],[@catchRating],[@routeRunRating],[@cITRating],[@specCatchRating],[@releaseRating],[@jumpRating],[@throwPowerRating],[@throwAccShortRating],[@throwAccMidRating],[@throwAccDeepRating],[@throwOnRunRating],[@playActionRating],[@tackleRating],[@hitPowerRating],[@powerMovesRating],[@finesseMovesRating],[@blockShedRating],[@pursuitRating],[@playRecRating],[@manCoverRating],[@zoneCoverRating],[@pressRating],[@kickPowerRating],[@kickAccRating],[@impactBlockRating],[@runBlockRating],[@passBlockRating],[@kickRetRating],[@toughRating],[@staminaRating],[@injuryRating])))

Worksheet is here: https://1drv.ms/x/s!Ah8EhteTsIhUiPks9FSrI5soPSB90w

I'm still getting a #VALUE error when trying to run this function. In evaluation, it looks like the individual values in our constructed array are evaluating to #VALUE for some reason. Any ideas why eval in our constructed array may be failing?

For a start, this part:

INDEX(Weights!$C$4:Weights!$X$47, 0, MATCH(C2, Weights!$C$4:Weights!$X$4,0))

will always include a non-numeric as the first entry, since you are including the header row (row 4 in the Weights sheet). Perhaps you meant:

INDEX(Weights!$C$5:Weights!$X$47, 0, MATCH(C2, Weights!$C$4:Weights!$X$4,0))

?

Secondly, the parentheses you have surrounding the choices for CHOOSE's values are erroneous. Should be:

CHOOSE({1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43},[@speedRating],[@strengthRating],[@agilityRating],...etc.

Regards

 

[keylimesoda]

In experiments, Excel seemed smart enough to skip the headers if I used $C$4? However, you're right that $C$5 is more correct.

Also, you're exactly right on the parenthesis--that fixed the issue.

Thank you so much for your help. Is there some form of credits or kudos that this board hands out? I'm new here, but I'd like to give proper credit.

 

[XOR LX]

You're welcome!

You can 'Like' my answer if you want. Not that it amounts to much in the long run, but I won't complain!

Cheers