3

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:

Various football club emblems

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

Improve this question

1 Answer 1

Reset to default
5

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))
Improve this answer
3
  • That's a nice and elegant solution. Thank you very much. I still didn't quite get why index combined with randbetween didn't work, I do understand that the function didn't quite work for what I intended though. Any chance of you giving another explanation for me? (I might research later more about it anyway, but if you have one, I'll be thankful).
    – Beauregard Lionett
    Commented Sep 15, 2015 at 1:43
  • 2
    Index with randbetween can work, if you give correct bounds for randbetween. The second argument of index is row number relative to the range you gave as the first argument. I gave an example of how your command =index(Spain!A$2:A$19, randbetween(2,19)) can throw an error: it's when the random number happens to be 19.
    – user79865
    Commented Sep 15, 2015 at 2:22
  • Mm, yeah, the relative was (and still is) confusing me, I made a test as the following: =index(England!A$2:A$21, randbetween(1:19)) and it works perfectly, randomizing between from the first (AFC Bournemouth) to the last one (West Ham), I thought it would show the name of the league instead (since it's on A1) and ignore West Ham, because it is out of the range between 1 and 19, but I think that's where relative plays its part. Thank you for your help and patience teaching me this. :)
    – Beauregard Lionett
    Commented Sep 15, 2015 at 4:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.