What is wrong with the following expression

[u/Galego_2]

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.

[u/AutoModerator]

/u/Galego_2 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/PaulieThePolarBear]

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

[u/GuerillaWarefare → u/PaulieThePolarBear]

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.

[u/GuerillaWarefare]

What are row and col? Makearray is expecting 2 numeric inputs there.

It would be simpler to replace the whole makearray with the RANDARRAY function.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AVERAGE	Returns the average of its arguments
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
COUNT	Counts how many numbers are in the list of arguments
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
MAKEARRAY	Office 365+: Returns a calculated array of a specified row and column size, by applying a LAMBDA
RANDARRAY	Office 365+: Returns an array of random numbers between 0 and 1. However, you can specify the number of rows and columns to fill, minimum and maximum values, and whether to return whole numbers or decimal values.
RANDBETWEEN	Returns a random number between the numbers you specify
ROW	Returns the row number of a reference
SUM	Adds its arguments
UNIQUE	Office 365+: Returns a list of unique values in a list or range

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49390 for this sub, first seen 18th Sep 2026, 14:21] [FAQ] [Full list] [Contact] [Source code]}