dynamic sums

  • Thread starter Thread starter Charles Puzzanchera
  • Start date Start date
C

Charles Puzzanchera

I am not sure if this subject line fits my problem but here goes. The first
row of my worksheet has values in descending order from 16 to 1 (starts in
col B and ends with col Q). Rows 2 on down contain text responses to items
which are ranked by participants (i.e., from 16 to 1). All participants are
asked to enter their response in capital letters. The ranked responses need
to be compared to an answer key. When the correct answer is entered, the
person is awarded the point value associated with their ranking; if the
answer is wrong, no points are added. The sheet is set up so that correct
answers in the answer key are in CAPS. Since the responses can be ranked
differently by different participants, I need a way to compare each row of
responses to the answer key, assign the appropriate point value, and create
a sum of these points.

I am not sure how to approach this task. I am not a VB guy so I need
something based on functions. This newsgroups has given me great assistance
in the past so I again look to it for counsel.

thanks in advance
chaz
 
Chaz
I kind of see where you are going here. You will probably need to use a Lookup function to compare the participants answers to the correct answer. If the response is not in the LOOKUP table Excel will return N/A#, so some IF formula will be needed to change this to a zero. You might also be able to use a straight IF statement if each question has only one correct answer

How do you know which question is being answered under each ranking? In order to check the answer, Excel will need to know which answer key to use. You could set up an area on the side, or a seperate sheet, which multplies the ranking by 1 for correct answers and 0 for incorrect. Then a simple SUM to put them all together

You can e-mail me at (e-mail address removed) if you need more help

Regards
Mark Graesse


----- Charles Puzzanchera wrote: ----

I am not sure if this subject line fits my problem but here goes. The firs
row of my worksheet has values in descending order from 16 to 1 (starts i
col B and ends with col Q). Rows 2 on down contain text responses to item
which are ranked by participants (i.e., from 16 to 1). All participants ar
asked to enter their response in capital letters. The ranked responses nee
to be compared to an answer key. When the correct answer is entered, th
person is awarded the point value associated with their ranking; if th
answer is wrong, no points are added. The sheet is set up so that correc
answers in the answer key are in CAPS. Since the responses can be ranke
differently by different participants, I need a way to compare each row o
responses to the answer key, assign the appropriate point value, and creat
a sum of these points

I am not sure how to approach this task. I am not a VB guy so I nee
something based on functions. This newsgroups has given me great assistanc
in the past so I again look to it for counsel

thanks in advanc
cha
 
Back
Top