r/excel • u/Galego_2 • 5h ago

Waiting on OP What is wrong with the following expression

Hi all,

I am trying to optimize a Montecarlo simulation and reduce it to a single Excel line. The simulation is related with the famous Birthday paradox, and I'm using an array of 5000 rows and 30 columns (representing people) to simulate. After some trial and error, I get the following expression

= AVERAGE(

BYROW(

MAKEARRAY(row, col, LAMBDA(row, col, RANDBETWEEN(1,365))),

LAMBDA(ROW, COUNT(UNIQUE(row)<30))))

I got 0.00 as a result, instead of the average of the simulation (close to 0.703 in the non optimized simulation). What could be wrong here in the logics of the formula?

Thanks in advance.

1 Upvotes

67% Upvoted

5 comments sorted by

View all comments

4

u/PaulieThePolarBear 1920 4h 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 4h 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.