0

I'm trying to do some bootstrapping with a data set in Excel with the formula =INDEX($H$2:$H$5057,RANDBETWEEN(2,5057)), where my original data set in is column H. It seems to work most of the time, but there is always approximately one cell that outputs a reference error. Does anyone know why this happens, or how to avoid including that one cell? I'm trying to generate a histogram from this data, and FREQUENCY does not play nice with an array with an error in it.

user3704693
  • 1

1 Answers1

1

Please try: 

=INDEX($H$2:$H$5057,RANDBETWEEN(1,5056))

=RANDBETWEEN(2,5057) returns a reasonably arbitrary value of 2 or any integer up to and including 5057. Used as above this specifies the position in the chosen array (H2:H5057) - that only has 5056 elements, so one problem would be when RANDBETWEEN hits on 5057. Much easier to observe with just H2:H4 and RANDBETWEEN(2,4).

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139