How do I EXCLUDE N/A Answers when calculating scores

[lmn]

Hello,
I have several surveys - and they have between 10 and 15 questions each.

On some of the surveys - only one question is applicable. Right now I count an N/A as if it were a yes, but if the vendor does bad on the only question that is applicable, it will still give them a high rating.

Is there an easy way to exclude N/A answers when calculating averages and scores?????

Thx,

 

[scriverb]

In a query you can create new calculated columns based on the survey type that will total up the Yes, No, N/A, and totals legitimate answers:

Select (IIF([Q1]="Yes",1,0) + IIF([Q2]="Yes",1,0) + IIF([Q3]="Yes",1,0) + IIF([Q4]="Yes",1,0) + IIF([Q5]="Yes",1,0)) as CountOfYes, (IIF([Q1]="No",1,0) + IIF([Q2]="No",1,0) + IIF([Q3]="No",1,0) + IIF([Q4]="No",1,0) + IIF([Q5]="No",1,0)) as CountOfNo, (IIF([Q1]="N/A",1,0) + IIF([Q2]="N/A",1,0) + IIF([Q3]="N/A",1,0) + IIF([Q4]="N/A",1,0) + IIF([Q5]="N/A",1,0)) as CountOfNA, [CountOfYes]/([CountOfYes]+[CountOfNO]) as CalcPercent
FROM tblQuestionnaire

This is an example of how you can rollup these figures.

Bob Scriver

 

[lmn]

Thanks for the help - unfortunately it's a bit more complex than that. Each survey has a question worth anywhere from 5-20% of the total value of the survey.

So if a question worth 20% if Yes or No is answered N/A - then the values of the survey are recalculated to equal 100% based on which ones were answered yes to. If this makes sense :-/ I need to also look at it per vendor and not as a whole (which, of course, would be much easier). To make it even more complex, I then need to rank the vendors on performance against one another - so any N/A answer couldn't be included in the averaging out/ranking....

For example

Vendor 1

Question 1: 1 pt (= yes) - 15% weighted value
Question 2: 1 pt (= yes) - 10% weighted value
Question 3: 1 pt (= yes) - 20% weighted value
Question 4: N/A - 30% weighted value
Question 5: 0 pt (= no) - 25% of weighted value

I would need to first recalculate the 0s and 1s to equal 100%, then recalculate the new values of each question based on that new percentage - no???

The problem the guy here has is that if he has 12 questions and 11 N/A answers but one negative answer - the survey should reflect a very low number. Right now, I have all of the N/As with the value the same as Yes - so it is giving them a good survey result. :-/

I know I am making no sense at all! ugh

~L

 

[GingerR]

i have done a similar thing (without the weighted answers) and this is what i did:
i have a table called SCORES.
the first field is TEXT (1,2,3,4,NA)
the second field is a NUMBER (1,2,3,4, {blank})

when the users chose their 'score' it was from the text field, shown to them in a combo box.
when i 'translated' or did any calcuations, the client wanted "NA's" excluded from averages just like you do.
it turns out the {blank} numeric value for NA would be handled like that whenever i put AVG in a query or report.
so, if in Customer Relations, Mary got
1
2
2
3
NA

her final score for this category was 2, not 8/5 whatever that is.

so make that table (or add a numeric field in a table you already have that has unique scores) and use that numeric field for your averages and stuff.

then for your weighting, i guess i'd go:

iif(Score="NA",null,"Score * Weight&quot

see if that works.