SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
olayemi
Topic Author
Posts: 17
Joined: June 17th, 2014, 4:27 pm

Shuffling arrays- EXCEL VBA

April 19th, 2015, 10:29 pm

Hi All,I have a mini-project pls kindly help me out. Am trying to shuffle this array and randomly pick two of these at once:numtop(1) = 1topsuit(1) = "H"numbck(1) = 2bcksuit(1) = "H"numtop(2) = 1topsuit(2) = "H"numbck(2) = 3bcksuit(2) = "H"numtop(3) = 1topsuit(3) = "H"numbck(3) = 4bcksuit(3) = "H"numtop(4) = 1topsuit(4) = "H"numbck(4) = 5bcksuit(4) = "H"numtop(5) = 1topsuit(5) = "H"numbck(5) = 6bcksuit(5) = "H"numtop(6) = 2topsuit(6) = "H"numbck(6) = 3bcksuit(6) = "H"so the output should look like :1H 3H2H 3HAnd also to be able to shuffle the arrays pls.
 
User avatar
Orbit
Posts: 492
Joined: October 14th, 2003, 5:34 pm

Shuffling arrays- EXCEL VBA

April 20th, 2015, 5:02 pm

Well I don't understand the substance of the problem, but it looks like you need to choose numtop(),topsuit(),numbck() and bcksuit(), each of which are indexed from 1 to 6.If the distribution of these choices is "even," (i.e. they occur with equal chance) then simply use the Excel function "=Randbetween(1,6)" for each index choice.HTH
 
User avatar
olayemi
Topic Author
Posts: 17
Joined: June 17th, 2014, 4:27 pm

Shuffling arrays- EXCEL VBA

April 20th, 2015, 5:11 pm

Yes that is what I thought too and did initially but my boss wants each of this array to be shuffled , picked at random and concatenated with the random suits to form a representation of a card such as : 1H 2S 2C 3Hand this should be the output on the spreadsheet.
 
User avatar
Orbit
Posts: 492
Joined: October 14th, 2003, 5:34 pm

Shuffling arrays- EXCEL VBA

April 20th, 2015, 6:32 pm

Ok again not really 100% sure what you mean, but maybe you mean sampling without replacement?Here is an excel user-defined function that will do that:http://www.mcgimpsey.com/excel/udfs/randint.htmlAlso google "sampling without replacement Excel"HTH
 
User avatar
olayemi
Topic Author
Posts: 17
Joined: June 17th, 2014, 4:27 pm

Shuffling arrays- EXCEL VBA

April 20th, 2015, 7:44 pm

Thank you so much. I have a strong feeling this will be very helpful. Cheers
 
User avatar
tagoma
Posts: 18351
Joined: February 21st, 2010, 12:58 pm

Shuffling arrays- EXCEL VBA

April 20th, 2015, 8:12 pm

So your 'boss' asked you that? What kind of companies are you working with?
 
User avatar
bearish
Posts: 5426
Joined: February 3rd, 2011, 2:19 pm

Shuffling arrays- EXCEL VBA

April 21st, 2015, 1:38 am

QuoteOriginally posted by: tagomaSo your 'boss' asked you that? What kind of companies are you working with?A six card monte shop?
 
User avatar
mutley
Posts: 745
Joined: February 9th, 2005, 3:51 pm

Shuffling arrays- EXCEL VBA

April 21st, 2015, 5:44 am

rarely do you find a stranger requirement posed here than in the opening post. who shuffles arrays?
 
User avatar
Cuchulainn
Posts: 61549
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Shuffling arrays- EXCEL VBA

April 21st, 2015, 6:15 am

QuoteOriginally posted by: mutleyrarely do you find a stranger requirement posed here than in the opening post. who shuffles arrays?On Wall Street?See also Knuth, Vol. II section 3.4.2. Algorithm S.You might want n unbiased records at random from a file containing N records? Don't know about VBA but in C++11 you could use stuff like shuffle
Last edited by Cuchulainn on April 20th, 2015, 10:00 pm, edited 1 time in total.
http://www.datasimfinancial.com
http://www.datasim.nl

Every Time We Teach a Child Something, We Keep Him from Inventing It Himself
Jean Piaget
 
User avatar
olayemi
Topic Author
Posts: 17
Joined: June 17th, 2014, 4:27 pm

Shuffling arrays- EXCEL VBA

April 21st, 2015, 10:10 pm

Thank you. ITS SORTED.Thank you.
 
User avatar
mutley
Posts: 745
Joined: February 9th, 2005, 3:51 pm

Shuffling arrays- EXCEL VBA

April 23rd, 2015, 6:08 am

Cuch, I have never once shuffled an array. I will inform you by carrier pigeon when I finally do :)
 
User avatar
Cuchulainn
Posts: 61549
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Shuffling arrays- EXCEL VBA

April 24th, 2015, 5:22 pm

QuoteOriginally posted by: mutleyCuch, I have never once shuffled an array. I will inform you by carrier pigeon when I finally do :)Mutley,There's only one thing worse than shuffling arrays and that's _not_ shuffling arrays.
Last edited by Cuchulainn on April 23rd, 2015, 10:00 pm, edited 1 time in total.
http://www.datasimfinancial.com
http://www.datasim.nl

Every Time We Teach a Child Something, We Keep Him from Inventing It Himself
Jean Piaget
 
User avatar
Polter
Posts: 2526
Joined: April 29th, 2008, 4:55 pm

Shuffling arrays- EXCEL VBA

April 25th, 2015, 7:45 am

You can even implement your own sorting algorithm in Excel:http://www.slideshare.net/Felienne/spre ... ru5d4GPk// Seriously, though, if you work with Excel spreadsheets on a daily basis some of the described practices should be mandatory -- e.g., naming things...
Last edited by Polter on April 24th, 2015, 10:00 pm, edited 1 time in total.
 
User avatar
Cuchulainn
Posts: 61549
Joined: July 16th, 2004, 7:38 am
Location: Amsterdam
Contact:

Shuffling arrays- EXCEL VBA

April 25th, 2015, 8:14 am

She's speaking American English. It's a bit exaggerated.
Last edited by Cuchulainn on April 24th, 2015, 10:00 pm, edited 1 time in total.
http://www.datasimfinancial.com
http://www.datasim.nl

Every Time We Teach a Child Something, We Keep Him from Inventing It Himself
Jean Piaget
 
User avatar
olayemi
Topic Author
Posts: 17
Joined: June 17th, 2014, 4:27 pm

Shuffling arrays- EXCEL VBA

April 25th, 2015, 8:38 am

Thanks
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On