Actuarial Outpost Help with Excel, please..
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

 Fill in a brief DW Simpson Registration Form to be contacted when our jobs meet your criteria.

#1
02-14-2007, 12:37 AM
 zeroEthix Member Join Date: Mar 2005 Posts: 2,073

I need a formula that will help create random letters down a column: A-F.

Probability of A=3%, B=6%, C=8%, D=11%, E=14%, F=16%

I've got it worked out to where each letter is a header of a column ("A" is in A1, "B" is in B1, etc.). My question is: how do I consolidate that in one column, so that, "A" for example, reads ABFCBACFBFBECEABCEABCEAFBCEFBCAFEBCFAEBFC...?

I'm using the random number generator for the 6 columns...thx
#2
02-14-2007, 12:50 AM
 hotkarl Full Fledged Member Join Date: Apr 2003 Posts: 8,174

a1&b1&c1&.....
__________________
Spoiler:
#3
02-14-2007, 01:18 AM
 Mark Cavazos Member Join Date: Sep 2004 Posts: 2,619

Why not do it more directly?

Put the random number function in cell A1

In cell B1 use the formula =if(A1<3%,"A",if(A1<9%,"B",if(A1<17%,"C",if(A1<28% ,"D",if(A1<42%,"E",if(A1<58%,"F","Hey, these probabilities don't add up to 100%"))))))

Repeat in as many cells as necessary.
#4
02-14-2007, 03:41 AM
 zeroEthix Member Join Date: Mar 2005 Posts: 2,073

Quote:
 Originally Posted by Mark Cavazos Why not do it more directly? Put the random number function in cell A1 In cell B1 use the formula =if(A1<3%,"A",if(A1<9%,"B",if(A1<17%,"C",if(A1<28% ,"D",if(A1<42%,"E",if(A1<58%,"F","Hey, these probabilities don't add up to 100%")))))) Repeat in as many cells as necessary.
I'd like to do it that way, but I may be working with more letters...14-16. Nesting only goes to 7 statements
#5
02-14-2007, 03:44 AM
 atomic Member CAS Join Date: Jul 2006 Posts: 4,088

Reading posts like these frustrates me to no end that Mathematica is not more widely used in the actuarial field. I could write a Mathematica command to do this in my sleep.
__________________
Spoiler:
"No, Moslems don't believe Jesus was the messiah.

Think of it like a movie. The Torah is the first one, and the New Testament is the sequel. Then the Qu'ran comes out, and it retcons the last one like it never happened. There's still Jesus, but he's not the main character anymore, and the messiah hasn't shown up yet.

Jews like the first movie but ignored the sequels, Christians think you need to watch the first two, but the third movie doesn't count, Moslems think the third one was the best, and Mormons liked the second one so much they started writing fanfiction that doesn't fit with ANY of the series canon."

-RandomFerret
#6
02-14-2007, 04:34 AM
 zeroEthix Member Join Date: Mar 2005 Posts: 2,073

I played with it more..

I took 65535 and multiplied by the probabilities (0-1) of each letter then rounded to the nearest whole number. I then made one column with that many a', b's, c's, etc. In the column next to the Master column, I used =rand() then sorted by rand(): this made the letters appear random instead of simply listed by their weighting (probability).
---------------
I can also encapsulate this in a 100-character string named, say, Choices, defined as

="AAABBBBBBCCCCCCCC..."

Then use the formula

= mid( Choices, randombetween(1,100), 1 )

to retrieve a probabalistically weighted result.
#7
02-14-2007, 06:47 AM
 General Apathy Revered CAS AAA Join Date: Jun 2006 Location: St. Kitts Studying for the Beach Favorite beer: Carib Posts: 28,708

Quote:
 Originally Posted by atomic Reading posts like these frustrates me to no end that Mathematica is not more widely used in the actuarial field. I could write a Mathematica command to do this in my sleep.
What's mathematica?
__________________
***k Juan

Spoiler:

]
#8
02-14-2007, 06:59 AM
 atomic Member CAS Join Date: Jul 2006 Posts: 4,088

Quote:
 Originally Posted by General Apathy What's mathematica?
Ah..... It is math geek heaven, where a million digits of pi dance in a clear blue fractally-generated sky, and Bessel functions graze freely on lush hillsides of the greenest grass in the shape of Voderberg tiles.
__________________
Spoiler:
"No, Moslems don't believe Jesus was the messiah.

Think of it like a movie. The Torah is the first one, and the New Testament is the sequel. Then the Qu'ran comes out, and it retcons the last one like it never happened. There's still Jesus, but he's not the main character anymore, and the messiah hasn't shown up yet.

Jews like the first movie but ignored the sequels, Christians think you need to watch the first two, but the third movie doesn't count, Moslems think the third one was the best, and Mormons liked the second one so much they started writing fanfiction that doesn't fit with ANY of the series canon."

-RandomFerret
#9
02-14-2007, 07:11 AM
 General Apathy Revered CAS AAA Join Date: Jun 2006 Location: St. Kitts Studying for the Beach Favorite beer: Carib Posts: 28,708

sounds cool.
__________________
***k Juan

Spoiler:

]
#10
02-14-2007, 09:27 AM
 Phil Site Supporter Site Supporter Join Date: Sep 2001 Location: not on board the Enterprise Posts: 5,664

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off

All times are GMT -4. The time now is 06:16 AM.

 -- Default Style - Fluid Width ---- Default Style - Fixed Width ---- Old Default Style ---- Easy on the eyes ---- Smooth Darkness ---- Chestnut ---- Apple-ish Style ---- If Apples were blue ---- If Apples were green ---- If Apples were purple ---- Halloween 2007 ---- B&W ---- Halloween ---- AO Christmas Theme ---- Turkey Day Theme ---- AO 2007 beta ---- 4th Of July Contact Us - Actuarial Outpost - Archive - Privacy Statement - Top