Perhaps a better way of doing the calculation:
Quote:
|
=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:
Quote:
|
=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!