COMPLICATED (I think) - How do I to make 100.000 Monte Carlo sims to make an average of - using RAND in LibreOffice?

asked 2018-08-04 12:12:06 +0200

vladase gravatar image

Hello Y'all!

To start: I'm an average user of Linux and LibreOffice (know to do simple formulas, but not very complex; i can install software and understand simple principles, don't know programming).

I'm trying to do a Monte Carlo simulation in Calc - in an .ods file. I don't know data bases - so this is the only option for me.

I've done what I consider 90%-99% - need help with the last 1%-10%.

I think I'm missing just 1 formula, but can't figure how to get to the end and is quite frustrating.

If you're the Guru of LibreOffice & math and don't need the whole BACKGROUND/PRESENTATION of the issue - just jump to the PROBLEM part. Help is very much appreciated!

BACKGROUND/PRESENTATION: Me and a colleague are comparing probabilities of events happening, which are presumed to be random, but which we consider to be influenced by unforeseen factors (environment/hidden abilities/etc), each having his own idea / formula how to express that.

Events can be something like "how many times we flip heads in 100 coin tosses" - in theory it should be close to 50/50 - but in practice maybe there are people that do it 60/40 or 70/30 on a regular basis/in certain conditions (eg: if he is calm/concentrating, or with a specific coin, or if it is warm, etc)

So far we have input and analysed the data I have; my estimations so far; the other guy's estimations so far; we've made the comparison between expected outcome by myself, expected outcome by him and actual outcome. Important note here is that our guesses are probability style - (not true/false) so the results we compare are also probability style, both positive (>0) and negative (<0) - not just 1 vs 0 vs -1.

like with the coin: i say this guy has 0.55 chance for heads on a coin toss (makes it 55 of 100 tries); he says 0.44. The guy tosses and makes 52 = the event is 0.52. I win (because my choice was closest to 52) the amount of 0.03 (=0.55-0.52)

Since the set of events considered is quite small (only 80 so far) and the variation of success both against each other and against actual outcome is very high - we can't draw an firm conclusion who is right more often (and by how much) as a general basis / on a very long term (consider years/lifetime) - which is the target of this project. If we had a big number of events (like 1.000.000 or 10.000.000) maybe we could, but we don't have that. So we considered comparing the difference in expectations vs random numbers (this is the Monte Carlo part. First time I try it, hope I got it right)

What I've done so far: - used RAND function to generate random numbers so we can compare ... (more)

edit retag flag offensive close merge delete