How to CALCULATE assist points

Technical Technical Discussion
#1

So, I realize this is well after the fact, but during the season, as we were developing a scouting database in excel, we were considering tracking each robot’s individual assist locations per cycle, using a 3x3 grid as shown in the rules. A student had proposed that coding such a system would be VERY complex, given the myriad of possible outcomes. Due to circumstances of various symmetries, etc. I understand that this amount is pared significantly. Anyway, we decided on going a different route and didn’t need it, but promised him that I would figure out a way to do so in the off season. I sat down last night and came up with a rather… ugly bit of code in excel that will in fact calculate it correctly (included below), however, I was wondering if anyone else had done this, or had suggestions for a far more STREAMLINED approach.

The 3x3 grid occurs in the range C4:E6, and at the bottom of each column, as well as to the right of each row,there is a cell with a “counta” function pertaining to its respective adjacent column or row.

The code:
=IF(OR(AND(COUNTA(C4:E6)>5,(COUNTIF(C7:E7,">0")+COUNTIF(F4:F6,">0")=6)),AND(COUNTIF(C7:E7,">0")+COUNTIF(F4:F6,">0")=6,(COUNTIF(C7:E7,"=3")+COUNTIF(F4:F6,"=3"))<>2,COUNTA(C4:E6)<>4)),30,IF(AND(COUNTIF(C7:E7,">0")+COUNTIF(F4:F6,">0")=6,COUNTA(C4:E6)=4,IFERROR(CELL(“contents”,INDIRECT(ADDRESS(MATCH(2,F1:F6,0),MATCH(2,A7:E7,0)))),0)<>0),30,IF(AND(COUNTIF(C7:E7,">0")>1,COUNTIF(F4:F6,">0")>1),10,0)))

Basically, I began with scenarios to give 3 assists, then added elements to eliminate those which would incorrectly score 30 given the set parameters. I then proceeded to do the same with the 2 assist situations.

Any input would be greatly appreciated!

#2

and though I’m not sure what language the FMS is written in, if anyone has access to that methodology, that too would be interesting/ helpful!

#3

Are you interested in general approaches or just better code in Excel?

#4

I don’t know how FMS did it, but here’s one approach:


1) Make the power set of pairs of (robot,location with ball)
2) Filter for sets where all robots in the pairs are unique and all locations are unique
3) Find the max cardinality of the remaining sets.
#5

You can abuse the fact that assists can be calculated on the diagonal of the grid, if you count the robot-zone combinations along a diagonal, you can find the number of assits for a given column arrangement. If you also swap the columns around, and take the max, you can find the total assists.

Here’s a quick example in python:


#~/usr/bin/env python

import sys

def main ():
    if (len(sys.argv) < 1 + (3*3)):
        return

    grid = [0,0,0], [0,0,0], [0,0,0]]
    for i in range(0, 3):
        for j in range(0, 3):
            grid*[j] = int(sys.argv[1+(i*3)+j])

    print "The combination:"
    printgrid(grid)
    print "Has", calcAssists(grid), "assists"

def calcAssists (grid):
    asstCombs = [0]*(3*2*1) # 3! Combinations

    asstCombs[0] = calcDiagonal([grid[0], grid[1], grid[2]])
    asstCombs[1] = calcDiagonal([grid[0], grid[2], grid[1]])
    asstCombs[2] = calcDiagonal([grid[1], grid[0], grid[2]])
    asstCombs[3] = calcDiagonal([grid[1], grid[2], grid[0]])
    asstCombs[4] = calcDiagonal([grid[2], grid[0], grid[1]])
    asstCombs[5] = calcDiagonal([grid[2], grid[1], grid[0]])

    return max(asstCombs)

def calcDiagonal (grid):
    return (grid[0][0] + grid[1][1] + grid[2][2])

def printgrid (grid):
    for i in range(0, 3):
        print str(grid*[0]), str(grid*[1]), str(grid*[2])

if __name__ == "__main__": main()

Hopefully you can adapt that to excel****

#6

Just curious, is there any way off actually obtaining/ accessing actual FMS source code? Also, is anyone reading this familiar with what language/ protocol it employs?

#7

I think that this is something that everyone wishes was available to FRC team. Unfortunately, the source code has not been publicly released.

#8

There is a good write-up from 2009 here: http://www.chiefdelphi.com/media/papers/2266 however the main FMS-DS-Robot communication loop hasn’t really changed so it’s still relevant for at least the 2014 FMS.

However this only specifies robot control, AFAIK the scoring system in the main FMS is not accessible by teams over the network.

#9

The FMS source is not available to anyone outside of FRC Engineering.

The FMS proper (i.e. the big server box) is written primarily in .NET (with an MVC front end). There are other pieces to the field electronics (such as the driver station boxes), but I’m not sure what those run.

#10

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 DECODEfunction 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!

#11

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.

#12

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.