Why is R giving different simulation results to Excel?

I'm new R and trying to migrate from using Excel.

I used the Data analysis toolpak > random number generator in Excel to run 100k simulation runs on a normal distribution with mean 200k and standard deviation 10k with a computer seed as 100.

I then ran the same in R using the following code:

set.seed(100)
simple_sim = rnorm(100000, 200000, 10000)

The simulation results were different. Please could someone explain why?

Do you mean that the series of numbers generated are not exactly the same? I don't think there is any reason to expect them to be the same. The distributions of the two sets of numbers should be very similar but Excel and R may well use different code for pseudo-random number generation.

Thanks.

That explains it. I didn't realize that that each program uses different pseudo random number generation. I thought picking the same seed on different programs would give the same results.

Excel and R use the same RNG by default (Mersene-Twister), so you should get the same results for both programs. Excel's RAND() function uses the system time as a random seed, but I believe the toolpak uses the VBA function RND which can accept a seed for reproducibility.

If it's important to you to have the results match between the two, the first step in debugging would be to simplify. Set a seed and generate 3 random uniform numbers between 0 and 1 on both. They should match. Then, try 3 standard normal values, again, I would expect them to match.

If everything is good at that point, I would look at the code to see if you have the same structure, e.g. you're doing the same randomizations the same number of times, in the same order, etc.

After that, there may be more complicated issues at play.

1 Like

Thanks for the reply.

I've done the simplified debugging as you've suggested.

Excel and R are still not matching for the uniform and normal.

I did notice something peculiar with Excel:

In the normal, the first random number for seed 1 generated at was roughly at -3 standard deviations. As the seed number increased, the number generated was increasing and getting closer to the mean and then on to +3 s.d.

I'm trying to migrate analysis in my company from using Excel to R. So I know I will get people saying why excel and r dont match for monte carlo simulation. Mathematically there is nothing wrong with excel or r, it's just something strange with the random generation.

What version of Excel?

I'm using office 365.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.