Problem with INDEX and RANDBETWEEN

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
Tabs and Dark Mode
cjc2112 in Discussions on
50 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
32 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
15 Replies
Dev channel update to 80.0.355.1 is live
josh_bodner in Discussions on
67 Replies