I'm simulating a dataset in Excel and want to create a column that only takes the values Yes or No. I want to make it so that 20% of the cells in that column are No and the remaining 80% are Yes. I want the No's to be randomly dispersed through the column, is there a way to do this?

CodePudding user response：

In Office 365 you could use =CHOOSE(IF(SORTBY(SEQUENCE(100),RANDARRAY(100))<=20,1,2),"No","Yes")

It creates a sequence of 100 numbers and randomly sorts them. If the value is 20 or less it shows (chooses) No, else Yes.

You can make it dynamic by having 100 replaced by ROWS([your range]) and 20 by ROWS([your range])*0.2

Edit: CHOOSE is not necessary, IF already takes care of it: =IF(SORTBY(SEQUENCE(100),RANDARRAY(100))<=20,"No","Yes") Thanks to Scott Craner

CodePudding user response：

Please share your work and so that we can guide you in the right path. To get started see =CHOOSE(RANDBETWEEN(1,2),"Yes","No"), this might give you some ideas.