CODEWITHSUNDEEP
excelexcel-formula
Germz002
Germz002

Reputation: 1

Excel formula challenge

Create an Excel formula that will randomly insert the symbol "✓" into a range of cells (B1-B5) based on a value entered in cell A1. For example, if the value 4 is entered in A1, then four cells within the B1-B5 range should have the "✓" symbol inserted at random.

i tried

=IF(ROW()-ROW($B$1)+1<= $A$1, "✓", "")

but the symbol is not randomly assign to each box

Upvotes: 0

Views: 101

Answers (1)

JvdV
JvdV

Reputation: 75930

You could try:

enter image description here

Formula in B1:

=SORTBY(IF(ROW(1:5)<=A1,"✓",""),RANDARRAY(5))

Or:

=SORTBY(REPT("✓",ROW(1:5)<=A1),RANDARRAY(5))

Strictly speaking the above handles the given restrictions well. Though, as pointed out below, a more rigorous option could be:

=LET(total_rows,5,total_ticks,4,SORTBY(REPT("✓",SEQUENCE(total_rows)<=total_ticks),RANDARRAY(total_rows)))

Where the 5 and 4 could also be references to given cells holding these integers.

Upvotes: 4

Related Questions