How do you randomly select a non-blank numerical value from a row (Excel)

1
votes

Does anybody know the excel formula how to randomly select a non-blank value in a row which has blank cells and cells with numbers?

Here is an example table to show:

     1,1  2,2  3,3  4,4  5,5

1,1 0 0 0

2,2 0 0

3,3 0 0

What I please need is for the excel formula to be able to randomly select one of the 0's at the 1,1 row (possible places are at columns 1,1 2,2 and 5,5), and then for example I would get back the 0 at the 2,2 column.

If possible, can you also recommend how to get excel to repeat (recursion) the same formula to now randomly choose one of the 0's at row 2,2, as that was the column it randomly chose the 0 from in the previous step.

1
excelrandomexcel-formularepeatformulas
What is the point using random here if all numbers are 0? – ttaaoossuuuu
@Taosique I have just used 0's for the purpose of this example. The 0's can actually be any number – user3812613
How big is your data set? – Dubison

1 Answers

1
votes

Say we have data in row #1 like:enter image description here

This is a combination of blanks and values. In A2 enter:

=IF(A1="","",1)

and in B2 enter:

=IF(B1="","",1+MAX($A$2:A2))

and copy across. This will "mark" the non-blank cells:

enter image description here

Then pick a cell not in the first two rows and enter:

=INDEX(1:1,0,MATCH(RANDBETWEEN(1,MAX(2:2)),2:2,0))