Home

Problem with INDEX and RANDBETWEEN

tpmcmahon
New Contributor

I am trying to sample randomly from a column of numbers using a combination of the INDEX and RANDBETWEEN functions. The formula was "=INDEX($C$15:$C$213,RANDBETWEEN(15,213)" - this was failing on a regular basis, returning "#REF!". I separated the two statements, to try to find where the error was occurring, and discovered that any time the RANDBETWEEN function returned a number >=200, the INDEX function returned that error. I shifted the data up so that the values were in rows 2 to 200 - same issue. Got rid of the header so they were in 1-199 and no problem. Has anyone else run into this? Is there a way around it? I would like to use this on a data set that exceeds 200 rows. I am using Office Home & Business 2016 and I last updated earlier today.

3 Replies

@tpmcmahon , thats correct behaviour. You have 199 cells in you range starting from C15. Thus INDEX(C15:C213,1) returns C15; INDEX(C15:C213,199) returns C213 and INDEX(C15:C213,200) returns error since you out of range

Thanls, @Sergei Baklan!  I must of had a brain cramp today - I don't know why I kept trying to set to worksheet row number rather than array row number.

@tpmcmahon , no problem, happens with all of us. Glad to help.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies