Function Returning #NUM! Error When I Call It from a Cell

Question

I have a function that has two passed in parameters--one is a single number from a single cell, and the other is a range, but all numbers. The function calculates the percent change between each of the passed in numbers and finds the average. It's not all one range because of where they reside on the Google Sheet. Anyway, I have tested it with internal variables and it works perfectly. I have tested the passed in parameters with TYPEOF for specific indexes in the range and with the single number and it always returns "number". I have tested the TYPEOF on the returned variable, and it is a number. But, when I run the function from a cell I get a #NUM! error that says "Result is not a number." What am I missing?

function avgROC(prc, prcs){
//This function calculates the percent differences between values in a range of cells and returns the average
  var avg = [];
  var tot = 0;
  var m = 0;
  var n = 0;
  
  //Get all the percentages between all the values in the range
  for(var i = 0; i<prcs.length; i++){
    if(prcs[i]==0){continue;}
    if(prcs[i+1]/prcs[i]-1==-1){continue;}
    avg[i] = prcs[i+1]/prcs[i]-1;
    m = m + 1;
  }
  //Find the final percentage between the last number in the range and the isolated number
  avg[avg.length]= prc/prcs[m]-1; 

  //Get the average of the percentages
  for(var j=0; j<avg.length; j++){
    tot = avg[j]+tot;
    n = n + 1;   
  }
  var r = tot/n;
  
  return r;
} 

In the cell I am typing "=avgROC(A1, A5:M5)", where A1 is something like 5.64 and the range is all similar values. I've also tried passing the range as a string and using the

var prices = SpreadsheetApp.getActiveSpreadsheet().getRange(prcs);

approach, but that gave the same results. Any thoughts?

Answer 1

Although, unfortunately, from your question, I'm not sure of your expected values, from your error message and your script, how about the following modification?

Modification points:

• From your showing script and In the cell I am typing "=avgROC(A1, A5:M5)", where A1 is something like 5.64 and the range is all similar values., in your script, the values of prc and prcs of avgROC(prc, prcs) are a number and 2-dimensional array like [[number, number,,,]].
• In the above situation, prcs[i] is an array.
• In the case of for(var i = 0; i<prcs.length; i++){,,,}, at the last loop, prcs[i+1] is undefined.

I thought that this might be the reason for your current issue of But, when I run the function from a cell I get a #NUM! error that says "Result is not a number.".

When these points are reflected in your script, how about the following modification?

Modified script:

function avgROC(prc, prcs) {
  prcs = prcs[0]; // Added
  var avg = [];
  var tot = 0;
  var m = 0;
  var n = 0;
  for (var i = 0; i < prcs.length - 1; i++) { // Modified
    if (prcs[i] == 0) { continue; }
    if (prcs[i + 1] / prcs[i] - 1 == -1) { continue; }
    avg[i] = prcs[i + 1] / prcs[i] - 1;
    m = m + 1;
  }
  avg[avg.length] = prc / prcs[m] - 1;
  for (var j = 0; j < avg.length; j++) {
    tot = avg[j] + tot;
    n = n + 1;
  }
  var r = tot / n;
  return r;
}

• I thought that by this modification, the error of #NUM! will be able to be removed.

Note:

• In this modification, it supposes that you put =avgROC(A1, A5:M5) into a cell as a custom function, and also, all values are the number values. Please be careful about this.

• In this modification, for example, it is supposed as follows. Please be careful about this.

  • prcs[i + 1] / prcs[i] - 1 is (prcs[i + 1] / prcs[i]) - 1, it is not prcs[i + 1] / (prcs[i] - 1).
  • avg[i] = prcs[i + 1] / prcs[i] - 1; is avg[i] = (prcs[i + 1] / prcs[i]) - 1;, it is not avg[i] = prcs[i + 1] / (prcs[i] - 1);.
  • avg[avg.length] = prc / prcs[m] - 1; is avg[avg.length] = (prc / prcs[m]) - 1;, it is not avg[avg.length] = prc / (prcs[m] - 1);.

• If the result value from the modified script was not your expected value, can you provide the sample input and output values you expect? By this, I would like to confirm it.