#REF! being added to formula by setFormula() instead of an actual reference: IF(NOT(ISBLANK(N3)),#REF!,0))

Question

I've created a script that generates markbooks for teachers in a school. The script adds formulas that convert 'raw marks' into individual test grades.

Additionally, an overview of how a pupil is doing over all the tests they have sat so far is calculated from a formula looks up the percentage to assign an overall grade.

The part of the script I've written to accomplish this is below.

The issue I am having is that although the formula is being constructed correctly, some of the cell references are not being added. Instead of the cell ref I'm just getting #Ref!

In the case of the example sheet below, the 3 #ref! should actually be O1, R1 and U1.

I've separated this part out in my script as a variable (totalRef) on it's own to try to help me debug.

My first thought was that this was because the formula was being added and ins some cases the column that was being referenced did not yet exist, but I've eliminated that and the problem still persists.

Example sheet: https://docs.google.com/spreadsheets/d/1QXDinhu6Ywlf0lNe3dZBLWMf0pxPrKHgTqfPeEfn7ug/edit?usp=sharing

  var noOfAssessments = assessments.length;
  var currentCol = 14;//first raw mark assessment column
  var startCumul= "";
  var endCumul = "";
  for (var no = 0; no < noOfAssessments; no ++)
  {
    var totalRef = columnToLetter(currentCol+1)+"1";
    Logger.log(years[y] + " TOTALREF IS "+totalRef);
    startCumul = startCumul + "(IF(NOT (ISBLANK("+columnToLetter(currentCol)+(fRow +1)+")),"+columnToLetter(currentCol)+(fRow +1)+",0))";
    endCumul = endCumul + "(IF(NOT(ISBLANK("+columnToLetter(currentCol)+(fRow +1)+")),"+totalRef+",0))"
    if (no < noOfAssessments - 1)
    {
      startCumul = startCumul + "+";
      endCumul = endCumul + "+";
    }
    currentCol = currentCol + 3;//3 IS THE NUMBER OF COLS BETWEEN ASSESSMENTS. THIS VAL MIGHT NEED TO BE CHANGE IF USING QLA
  }

  var wholeCumulFormula = "=IFERROR(LOOKUP(ROUND((" + startCumul + ")/("+endCumul+")*"+getYearPercentages(years[y],setupData)+"),{";
  //add the base percentage boundaries
  var pBoundaries = getPercentageBoundaries(setupData);
  wholeCumulFormula = wholeCumulFormula + pBoundaries + "},{\"1C\",\"1B\",\"1A\",\"2C\",\"2B\",\"2A\",\"3C\",\"3B\",\"3A\",\"4C\",\"4B\",\"4A\",\"5C\",\"5B\",\"5A\",\"6C\",\"6B\",\"6A\",\"7C\",\"7B\",\"7A\",\"8C\",\"8B\",\"8A\",\"9C\",\"9B\",\"9A\"}),\"U\")";

  if (!yearData[fRow][12])//only overwrite blank cells
  {
    yearSheet.getRange(fRow+1,13,1,1).setFormula(wholeCumulFormula);
  }

This is the function used to create the ref:

function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}