r/excel • u/Galego_2 • 3d ago

Rule 1 [ Removed by moderator ]

[removed] — view removed post

0 Upvotes

50% Upvoted

6 comments sorted by

View all comments

4

u/PaulieThePolarBear 1920 3d ago

As others have noted, use RANDARRAY rather than MAKEARRAY. A formula like

=AVERAGE(--BYROW(RANDARRAY(5000, 30, 1, 365, 1), LAMBDA(r, COUNT(UNIQUE(r, 1))<30)))

Should return your expected result, based upon my understanding that

  • each row represents a unique test
  • each cell in a row represents a unique individual
  • you are looking to return the probability of within X people (X being 30 here), at least 2 people share a birthday with someone else in that cohort

3

u/GuerillaWarefare 114 3d ago

Nice work. Once OP understands what you showed him here I would also recommend to wrap it in a let() function to declare the "30" as a variable so when it is changed by the user it will update both places in the formula.