How to CALCULATE assist points

[Jon Stratis]

Perhaps a better way of doing the calculation:

=VLOOKUP(MIN(IF(COUNTA(C4:C6)>0,1,0)+IF(COUNTA(D4: D6)>0,1,0)+IF(COUNTA(E4:E6)>0,1,0), IF(COUNTA(C4:E4)>0,1,0)+IF(COUNTA(C5:E5)>0,1,0)+IF (COUNTA(C6:E6)>0,1,0)),G3:H6,2)

In this, I included the counta functions, although you could certainly utilize the ones to the side and bottom of the table you mentioned, which makes this equation even easier:

=VLOOKUP(MIN(COUNTIF(C7:E7, ">0"), COUNTIF(F4:F6, ">0")),G3:H6,2)

Basically, we want to know how many rows and how many columns have something in them. From there, we could look at each possible combination exhaustively... but it's easier to look at the minimum of the two, as that will be the number of credited assists. Then I just created another table G3:H6 that has 0-4 in the first column and the corresponding scores in the second (0,0,10,30). Doing a vlookup on that second table lets me find the score that matches the number of assists performed and return it.

Note: Why doesn't excel have a function like Oracle's DECODE function in SQL? I so wanted to use that for this solution!

Edit: I just realized there's an edge case this doesn't handle correctly - two robots both poses in zone 1, then a third possesses in zones 2 and 3. Back to the drawing board!

[Jon Stratis]

Ok, second try now that I had a few more free minutes...

=VLOOKUP(MAX(COUNTA(C4,D5,E6), COUNTA(C4,E5,D6), COUNTA(D4,E5,C6), COUNTA(D4,C5,E6), COUNTA(E4,C5,D6), COUNTA(E4,D5,C6)),G3:H6,2)

Again, using the vlookup function to transition from number of assists to points. Here though, I'm simply doing a counta on each possible combination for 3 assists. By default, that also includes all possible combinations of 2 and 1 assists. By doing a max, we find the maximum possible assists that were counted.

[AdamHeard]

Originally Posted by Jon Stratis

Ok, second try now that I had a few more free minutes...



Again, using the vlookup function to transition from number of assists to points. Here though, I'm simply doing a counta on each possible combination for 3 assists. By default, that also includes all possible combinations of 2 and 1 assists. By doing a max, we find the maximum possible assists that were counted.

Tangentially related. Index(match()) is much more efficient than vlookup, and a more robust way to link in case people insert collumns, etc... I find it more intuitive to setup as well.

The performance is far better. Not a huge issue for most excels, but a huge performance difference in google spreadsheets.