I have this table in cells A1:E5, I would like to randomly select a value out of cells which are non blank.
Please help.
>Solution :
If you have Office 365 you could use:
=LET(a,TOCOL(A1:E5,1),INDEX(a,RANDBETWEEN(1,ROWS(a))))
Or =LET(a,UNIQUE(TOCOL(A1:E5,1)),INDEX(a,RANDBETWEEN(1,ROWS(a))))
If you want each number to have the same chance, regardless the times being repeated in the range.
The idea is: TOCOL with ignore blanks argument (1) will filter the range for values only. If you rand between 1 and the number of (unique) values, you can index that array of non-blank values and return the random row’s value within that array.