AVLOOKUPNTH and RANDBETWEEN cannot get along.

[John Brown]

Hi, a formula containing AVLOOKUPNTH is returning:

#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position,#ERROR: Expected long integer for Position

The reason seems to be that in the place for the Position parameter, I have put:

RANDBETWEEN(-1,-2)

Now this ought to return -1 or -2, which are valid values for Position.

If I replace this with either -1 or -2, I get correct results.

I have a hunch that the function's interpretation of Position is being unnecessarily fussy. Has it something to do with RANDBETWEEN being a volatile function?

If I replace

RANDBETWEEN(-1,-2)

with

IF(RANDBETWEEN(0,1),-1,-2),

it works.

Regards

John

[Charles Williams]

RANDBETWEEN(-1,-2) returns #NUM - try RANDBETWEEN(-2,-1) 

The smallest number must come firt.