risk assessment

[Me]

Hi I use a paper form at work that asks 3 multiple choice questions. The
answer to each question is given a score, and the total score is then used
to calculate whether risk is low, medium, high or very high.

What I'd like to do is set this up in Excel 2002 so that the user simply
clicks to select their choice for each question, and the score then results
in the assessed risk level being printed on screen.

Can anyone please either give me an outline of how to do this, or point me
to where I can find a sample that I could modify.

My email address as shown in newsgroups is fictitious. But I can display a
little used address if anyone is able to send me a sample.

Many thanks

 

[Max]

Perhaps this set-up will ease you-in ..

(Just post a readable version of your email here if you'll
like to have a copy of the sample book via private email)

----------
Setting up the Answer sheet
(To be hidden away from view later)
----------------
In Sheet1
-----------
Set-up in A16
the question and answer matrix, viz.:

....Q1 Q2 Q3
A 0 75 100
B 25 100 50
C 50 25 0
D 75 50 25
E 100 0 75

The figures inside the matrix are sample scores assigned depending
on the choice (A,B,C,D or E) selected for the particular question

Select A1:A6 and name this range as: MyList
(To name, with the range selected, click inside
the namebox just to the left of the formula bar,
type: MyList, and press Enter)

MyList will be used as the source range for the
data validation drop lists we're going to create in the
Question sheet for the users to select the choice.

--
Set-up in F1:G5, the risk-score table:

RiskLevel..Score
Low...............0
Medium.......75
High...........150
Very High..225

assuming the risks are defined as:
Low : >=0 and <75
Medium : >=75 and <150
High : >=150 and <225
Very High : >=225

-----------------------------
Setting up the Question sheet
-----------------------------------
In Sheet2
-----------
Assume the question / multiple-choice set-up
is in A16 and will look something like this:

.....Q1 text...Q2 text..Q3 text
A answer1 answer1 answer1
B answer2 answer2 answer2
C answer3 answer3 answer3
D answer4 answer4 answer4
E answer5 answer5 answer5

----------
Setting up the answer area
---------------------------------
Put a label "Select Answer" in B8
and list "Q1","Q2","Q3" down in A9:A11

Select B9:B11
Click Data > Validation
Under "Allow:", select List
In "source:" box, put: =MyList
Click OK
(This creates the 3 DV drop lists for Q1 to Q3)

---------
Finally .. the outputs area
--------------------------------------
Put the labels "Risk Level" and "Score" in B13 and C13

Put in C14:
=IF(OR(B9="",B10="",B11=""),"You must answer ALL Questions",
OFFSET(Sheet1!$A$1,MATCH(B9,Sheet1!A:A,0)-1,
MATCH(A9,Sheet1!1:1,0)-1)+
OFFSET(Sheet1!$A$1,MATCH(B10,Sheet1!A:A,0)-1,
MATCH(A10,Sheet1!1:1,0)-1)+
OFFSET(Sheet1!$A$1,MATCH(B11,Sheet1!A:A,0)-1,
MATCH(A11,Sheet1!1:1,0)-1))

Put in B14:
=IF(ISTEXT(C14),"-",
OFFSET(Sheet1!$G$1,MATCH(C14,Sheet1!G:G,1)-1,-1))

C14 will return the sum of the individual scores
for the answers selected for Q1 to Q3

B14 will return the Risk Level based on the score in C14

C14 will return the error msg "You must answer ALL Questions"
if any of the 3 questions are left unanswered
(i.e. if either of B9:B11 are left "blank")
and B14 will return "-" for such cases

Hope the above provides some ideas you can make use of

 

[Me]

Max that looks great - I've setup what you posted and I think I can see how
to adapt it to the actual form. Can't do too much more now for a couple of
days, but will Ping you at some point if i may, if I have problems, or to
ask for help with the next bit - there is another stage in the paper form
which sometimes alters the initial risk assessment.

Not sure what the "sample book" is that you refer to, but if it will help my
learning, please email details to me at
(e-mail address removed) - after removing phoney

You're a star!

 

[Max]

You're welcome, John !
Thanks for the feedback

I've just emailed the sample book to your id.
Its essentially what was described in the post ..
... just in case you get stuck somewhere
replicating the suggested construct <g>