Google表格：重复指定函数N次并对结果求和的方法

Hey there! Let's walk through exactly how to run your =IF(RAND()<0.25,1,0) function 100 times (or any N times) and sum up the outputs in Google Sheets. Here are a few simple, effective methods to do this:

Method 1: Use RANDARRAY + ARRAYFORMULA + SUM #

This is the most straightforward approach, since RANDARRAY lets you generate exactly N random numbers in one go. Here's the formula for 100 iterations:

=SUM(ARRAYFORMULA(IF(RANDARRAY(100)<0.25,1,0)))

• RANDARRAY(100) generates 100 decimal values between 0 and 1 (just like running RAND() 100 times individually)
• The IF statement checks each random number: returns 1 if it's less than 0.25, else 0
• ARRAYFORMULA ensures the IF runs across all 100 values instead of just one
• SUM adds up all the 1s and 0s to get your total count of "hits"

Method 2: Simplify with SUMPRODUCT #

SUMPRODUCT can handle array operations without needing ARRAYFORMULA, making the formula shorter. You can use it in two ways:

Option A (using boolean conversion): #

=SUMPRODUCT(--(RANDARRAY(100)<0.25))

The double hyphen (--) converts the TRUE/FALSE results of the <0.25 check into 1s and 0s, which SUMPRODUCT then sums up.

Option B (explicit IF): #

=SUMPRODUCT(IF(RANDARRAY(100)<0.25,1,0))

This works just like the first method, but SUMPRODUCT eliminates the need for ARRAYFORMULA.

Method 3: Make N Dynamic (Use a Cell Reference) #

If you want to easily adjust the number of iterations (instead of hardcoding 100), store N in a cell (say, cell A1) and reference it in the formula:

=SUM(ARRAYFORMULA(IF(RANDARRAY(A1)<0.25,1,0)))

Now you can just change the value in A1 to run the function 50, 200, or any number of times you want.

Quick Note About Volatility #

Keep in mind that RAND() and RANDARRAY() are volatile functions—this means every time you edit your sheet, refresh it (press F9), or open it, the random numbers will regenerate, and your sum will change. That's expected behavior for random probability tests!

内容的提问来源于stack exchange，提问作者Joseph Astrahan