Google Sheets – Fix ‘Parameter X Value Y is Out of Range’ Error

google sheets

I'm making a random team selector but I'm getting a #NUM! error saying that "Parameter 2 Value 20" or "Parameter 2 Value 25" is out of range. From what I understand, they are not.

The parameters referred by the function are in other sheets.. Here is the link to it. Example:

Do you think this is a bug or am I doing something wrong?

Best Answer

I see commands like

=index(Spain!A$2:A$19, randbetween(2,19))

The second parameter of index is not the absolute row number but is relative to the range specified as the first argument. For example,

=index(A$2:A$19, 1) refers to A2
=index(A$2:A$19, 18) refers to A19
=index(A$2:A$19, 19) throws an error

To fix this, either change the range of randbetween:

=index(Spain!A$2:A$19, randbetween(1,18))

or, if you want to refer by absolute row numbers, use indirect:

=indirect("Spain!A"&randbetween(2,19))

Related Solutions

Google-sheets – In Google Spreadshets, how can you loop through all available sheets not knowing the total count

If you don't mind using a macro, you can do the following. Add this script to your spreadsheet (Tools -> Scripts -> Script editor):

function getTotalSum(cell) {
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var sum = 0;
    for (var i = 0; i < sheets.length ; i++ ) {
        var sheet = sheets[i];
        var val = sheet.getRange(cell).getValue();

        if (typeof(val) == 'number') {
            sum += val;   
        }       
    }

    return sum;
}

and use the formula =getTotalSum("A1"), where "A1" is the cell which you want to sum up in all the sheets (except the one you putting the formula in).

I made a test script which you can take a look at: https://spreadsheets.google.com/ccc?key=0Ao3E95ou1MgbdG45blRvWlVaYkxFY01PQUhvZkZQcEE&hl=en&authkey=CPyZqxk

Google Sheets – Data Ranges in Custom Functions

That's because the getRange method only accepts a string as a range. Your problem can be resolved by using just plain JavaScript:

Code

function customSum(range) {
  var sum=0;  
  for(var i=0, iLen=range.length; i<iLen; i++) {
    for(var j=0, jLen=range[0].length; j<jLen; j++) {
      sum += range[i][j];
    }
  }  
  return sum;
}

Example

I've created an example file for you: Custom Sum

Remark

Please note the difference between the two pieces of code. No API calls are used. The reference you use (cells) has more than one column !! If you want that, then you need to take that into account as well. Also note that changes in the range will be calculated immediately.