How to Skip Blank cells while using "Randbetween" function

Hi,
I have the below formula in excel, and am facing problem to skip empty cells while random selection
=INDEX(H3:H25,RANDBETWEEN(1,COUNTA(H3:H25)))

Any body can help?

Thanks

Hi,
Thanks for the reply, but when doing the formula, when pressing CTRL & Shift then enter, i get a name from the list, doing F9 again, the cell value is changed to another name randomly, press F9 again, I get either "0" as value or the last name appeared is not changing

any reason why

Can you check this file

Thanks alot, it is working fine

FYI, the formula throws an error if any of the cells contain an error (#DIV/0, #NAME?, #VALUE!, etc.)

Simplest solution is obviously to clean up those errors.

Originally Posted by XOR LX

Hi,

Array formula**:

=INDEX(H:H,INDEX(MODE.MULT(IF(H3:H25<>{"",""},ROW(H3:H25))),RANDBETWEEN(1,SUM(N(LEN(H3:H25)>0)))))


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Hi, I just joined after searching for a solution to my problem. I am using MS Office Excel 365. I first started with this formula =INDEX(C4:C18,RANDBETWEEN(1,15)). When I have less than 15 names to choose from I sometimes get a random value of zero. I used the Array above but on occasion the random value generated is zero instead of a name. I have entered the formula as an Array for the range I need. In my current example I have a maximum of 15 cells to randomly select a name but in this case I only have 8 names to randomly select, the rest are blank. The number of names can change from day to day from 4 to 15 names. My blank cells in my Array do contain formulas referencing another tab ie. ='Lookup Data'!H10 if that makes a difference.

This is my Array formula based on the solution provided in a previous post.
{=INDEX(C:C,INDEX(MODE.MULT(IF(C4:C18<>{"",""},ROW(C4:C18))),RANDBETWEEN(1,SUM(N(LEN(C4:C18)>0)))))}

Hopefully I have provided enough information.

Thank you for any help.

Dave