Hello there,
I am trying to generate random numbers on Number for Mac, distributed around a mean with a given st. deviation. But there is no function i can find that allows me to do that like on Excel! Any solution about that?
It seems i can only generate random numbers evenly distributed.
Thank you
MacBook Pro (13-inch Mid 2012), iOS 10.3
Hi Tommy,
Just to kick this discussion off, perhaps some preparation before applying the RANDBETWEEN function may lead to a solution.
Values in the Body Cells in column B are entered (typed or pasted).
Formula in Footer Cell B12 = AVERAGE(B)
Formula in Footer Cell B13 = STDEV(B)
Formula in G2 (and Fill Down)
=RANDBETWEEN(E2,F2)
Regards,
Ian.
Hi Tommy,
This seems to do the job:
Enter the formula in B2, fill down for as many rows as you want samples in your set. The set shown has 1000 'samples'.
Syntax for NORMINV:
NORMINV(probability, average, stdev)
Details on the function (also supported in MS Excel, and recommended for this purpose in at least one Youtube posted video) are available in the Function Browser, which opens in the right sidebar when you enter = in a cell.
Regards,
Barry
PS: Realized after posting I had neglected to include the COUNTIFS formula in column D.
Enter in D2, Fill down for 10 rows:
COUNTIFS(B,">"&10×(ROW()−2),B,"<="&10×(ROW()−1))
B.
Hi Tommy,
Column D is a frequency distribution table used as a rough check on whether the generated values in column B resembled a normal distribution.
The formula counts the values in each 10 point range whose top value is listed in column C.
Not necessary, but it could provide a graph showing the distribution:
If you want to see the effect of repeated calculations of the values, include the frequency table and the graph. then format one of the unused cells on the table as a checkbox. Toggling the checkbox will recalculate the table, and the changes will be immediately reflected in the chart, The chart is an X-Y scatter chart. The only changes I made in the chart as produced automatically were to spcify that the data points be connected with a curve, increase the number of steps in the X axis from the default 4 to 10, and to compress the height of the chart by pulling the top boundary downward.
Regards,
Barry
Dear Ian,
I find your reply very helpful, but as you know when you apply a normal distribution usually the values are more spread than 1 standard deviation, they might also be higher or lower that 2xSTDEV or 3xSTDEV. Obviously with lower probability... and this method you advise doesn't let me get to my point.
Moreover using your method you just create an evenly distribution between two values, while i have to get more values around the mean, they must not be equiprobable.
Dear Barry,
I thought about using random function in NORMINV too, you confirm me that it works than! thank you! only thing I didn't understand is what you did in column D. Could you explain me? Is it necessary?
Thank you
Random numbers on Numbers
