mardi 24 février 2015

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

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.





Aucun commentaire:

Enregistrer un commentaire