Random Number Generator with flexible probability

[Jayem1]

Hello,


I am not sure of the proper terminology of what I am looking for but here is a description...


I am looking to create a column of 1,000 results with each cell showing one of two numbers (ideally for me would be a "1" and a "-1"). I would like to be able to change what percentage of the time the "1" shows up, ie. in each cell there could be a 60% chance that the "1" would show up meaning there would be a 40% chance of the "-1" showing up.




Currently I am using the formula: =RANDBETWEEN(0, 1) to create what I hope is a 50/50 chance of either a "0" or a "1" showing up in each cell. I could not get the "-1" to work right but can work around that if it is not possible. The "0" and "1" will work fine.




This is for a trading scenario similar to a heads or tails (win /loss) simulation but with the ability to program the "win" percentage rate.


Is this possible? If so how do I do it?


Thank you,


Jason


PS. I did look at the other threads but could not figure out what would work in my situation.

 

[AhoyNC]

Try

Excel Workbook
	A
1	1
Sheet

 

[Jayem1]

Thank you that works perfectly.

Why do we need the "+0"?

 

[shg]

=if(rand()<0.6, 1, -1)

 

[Jayem1]

FYI: If I don't add the "+0" at the end of the formula it still generates the number but in a format that I cannot add the results with. If I make a column of numbers the SUM of that column always comes out zero. But when I add "+0" to the formula I am able to add up the results.

 

[shg]

The formula in post#2 returns text without the +0; the one in post 4 returns a number.

 

[Jayem1]

Thanks, I see what I did. I left the quotation marks in.

I put: =IF(RAND()<0.6,"1","-1")

Instead of: =IF(RAND()<0.6, 1,-1)

 

[shg]

You're welcome.