Random Number Generation & VLOOKUP

[Gareth_80]

Hi there,

I have been shown how to use the VLOOKUP function and the Random Number
Generation table to pick a random selction of questions and answers from a
data range. I keep getting the message "Numbers in discrete probability
column must have a sum of 1" but I have no idea what that means.
Unfortunately the person is no longer available and, try as I might, I can't
find any resource to tell me (in plain English) what the fields actually
mean. Can anyone please help?

Kind regards

Gareth

 

[Joel]

Post the functions if you are able.

Probability is based sum of all combinations equalling 1.

If you have 10 marbles
4 - Red = 4/10 = .4
2 - yellow = 2/10 = .2
3 - blue = 3/10 = .3
1 - green = 1/10 = .1

1 = .4 + .2 + .3 + .1

where .4 is the probability of picking a Red marble
where .2 is the probability of picking a Yellow marble
where .3 is the probability of picking a Blue marble
where .1 is the probability of picking a Green marble

The error just says your marbles do't equal 1.

 

[Mike Middleton]

Gareth -

The Discrete option of the Random Number Generation tool (part of the
Analysis ToolPak add-in) will generate static random numbers from a
specified discrete probability distribution. Click Help on the Random Number
Generation dialog box to see descriptions of the various options and how the
data must be arranged. For example, before using the Discrete option, you
should enter the values and probabilities of your discrete distribution into
adjacent columns of your worksheet, with values on the left and
probabilities on the right. And, those discrete probabilities should sum to
1.000.

You do not need to use the VLOOKUP worksheet function to obtain static
random numbers. The VLOOKUP function is sometimes used with RAND to obtain
dynamic random numbers (so you get a different set of random numbers each
time you press the F9 key).

If you need more information, please describe what results you want in more
detail. Also, describe how your data is arranged, what you are doing, and
what happens or doesn't happen.

- Mike
http://www.MikeMiddleton.com

 

[Gareth_80]

Hi Joel & Mike. Thanks for your answers, looks like I was a bit vague on what
i'm trying to achieve.
My Data is:
Column A ascending numbers 1.1 - 210.9 (but some are missing in the
sequence) Column B is a question (each one relating to a number)
Columns C, D, E & F are the 4 multiple choice answers
Column G is the Answer (A, B, C, D)

Each number relates to a checklist (ie: Checklist 1 has 5 questions,
1.1/1.2/1.3/1.4/1.5). These checklist make up various training modules - I
wanted to be able to create a look up for my team whereby they can click a
button for a module (Ie, Module 16) and have Excel automatically look up the
related checkists that make up Module 16 and create a test from the questions
- but the test to be 'random' from the number of questions per checklist. So
if checklist 1 has 5 questions, I'd only ever want 3 'random' questions
picked per test.

I thought I could use a macro to run Vlookup to break the module number
request down into checklists (this would be a simple table in another
worksheet)
The Match/Vlookup to find the related questions and finally the Random
number generator to pick 3 out of 5 matches and place them into a new
worksheet.

I'm ok on Excel but no big whizz - is this something that's a little too
complicated for the intermediate user, I would like to have a go, guessing I
just need to know where to start?

Thanks for any advice you can give me.

Kind regards

Gareth

 

[Gareth_80]

I've changed the format slightly. I've now got a main page and numerous tabs,
each tab represents a module number. Within each module tab is my set of
checklist questions for that module. I've hyper linked the main page numbers
to the corresponding module tab.
The questions are numbered as checklist number point acending numerical
value. Ie: 19.1, 19.2 etc would be checklist 19-question 1, checklist
19-question 2 etc
Is it possible to use the random generator to select a set of x amount of
questions from that selection of data?
I was thinking I could record the task with a macro, repeating the process
for however many checklists make up the module so I get a selection from all
of them and link the macro to a button.
The questions could go into a seperate page and I'm sure I could work out a
way of merging that data into our standard test format (thinking using
various "=(cellname)" in a seperate tab which contained the template &
formatting (would it keep the formatting of the original cell or what I set
it to in that tab?)
Got so many questions as to if this could work, it works in my head but not
sure of the formulas needed. Still having issues getting my head around the
need for 'probablilities' adding up to 1. Understand the principle that the
variable for a computer 'random' number should = 1 but can't seem to apply it
to this. Should my 'checklist' numbering add up to 1 within the selected
range in the random number generator? so if they're called 19.1 etc it
wouldn't work? I've checked the 'help' sections but as said above, I can't
seem to apply it to this.

Sorry to be a pain all, really want to be able to understand this.