函数返回#NUM!当我从单元格调用它时出现错误

Question

我有一个函数，它有两个传入参数——一个是来自单个单元格的单个数字，另一个是一个范围，但都是数字。该函数计算传递的每个数字之间的百分比变化，并找到平均值。因为它们在Google表单上的位置不同，所以并不都是一个范围。无论如何，我已经测试了它与内部变量，它的工作完美。我已经用TYPEOF测试了传入的参数，用于范围内的特定索引和单个数字，它总是返回&;number&;。我已经测试了返回变量的TYPEOF，它是一个数字。但是，当我从单元格运行函数时，我得到一个#NUM!错误提示"结果不是数字"。我错过了什么?

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;
} 

在单元格中，我键入"=avgROC(A1, A5:M5)"，其中A1类似于5.64，范围都是类似的值。我还尝试将范围传递为字符串并使用

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

方法，但得到了相同的结果。任何想法吗?

Answer 1

虽然，不幸的是，从你的问题，我不确定你的期望值，从你的错误消息和你的脚本，如何修改以下?

修改点:

• 从您的显示脚本和In the cell I am typing "=avgROC(A1, A5:M5)", where A1 is something like 5.64 and the range is all similar values.，在您的脚本中，avgROC(prc, prcs)的prc和prcs的值是一个数字和二维数组，如[[number, number,,,]]。
在上述情况下，prcs[i]是一个数组。
• 对于for(var i = 0; i<prcs.length; i++){,,,}，在最后一个循环时，prcs[i+1]是undefined。

我想这可能是你们现在发行But, when I run the function from a cell I get a #NUM! error that says "Result is not a number."的原因。

当这些要点反映在您的脚本中时，下面的修改如何?

修改脚本:

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;
}

• 我想通过这个修改，可以消除#NUM!的错误。

注意:

• 在此修改中，假设您将=avgROC(A1, A5:M5)作为自定义函数放入单元格中，并且所有值都是数值。请小心点。

• 以这个修改为例，假定如下:请小心点。

  • prcs[i + 1] / prcs[i] - 1是(prcs[i + 1] / prcs[i]) - 1，而不是prcs[i + 1] / (prcs[i] - 1)。
  • avg[i] = prcs[i + 1] / prcs[i] - 1;是avg[i] = (prcs[i + 1] / prcs[i]) - 1;，不是avg[i] = prcs[i + 1] / (prcs[i] - 1);。
  • avg[avg.length] = prc / prcs[m] - 1;是avg[avg.length] = (prc / prcs[m]) - 1;，不是avg[avg.length] = prc / (prcs[m] - 1);。

• 如果修改后的脚本的结果值不是您期望的值，您能提供您期望的样例输入和输出值吗?因此，我想确认一下。