Converting 'YES' / 'NO' text into numerical values

[sclayden]

Help!!!!

I have set up a sheet with questions and have got it to work so that if you enter 'YES' a score of 1 is given and if you enter 'NO' a score a zero is given. The formula I have used is "IF (D7>=1,1,0)

From this a potential score is given (e.g. 10 questions all with a potential score of 1 = 10), an actual score (e.g. 7 questions answered 'YES' = 7) and from this a percentage result (e.g. 7 out of 10 = 70%)

However, if I want to mark a question 'NOT APPLICABLE' how do I get it to not give that question a score and for it not to affect the overall potential score?

Thanks (I hope!!)

 

[Brian from Maui]

If I want to give question one a score of '1' i'm using the formula =IF(D7="NA","",IF(D7>=1,1,0))

If I want to give question two a score of '3' i'm using the formula =IF(D7="NA","",IF(D7>=1,3,0))

etc. etc.

What would be the total points possible be for the 10 questions? Or what would you base your percentage on? Answering 7 of 10 questions for %70 or 70 points out of a possible 100?

 

[Aladin Akyurek]

If I want to give question one a score of '1' i'm using the formula =IF(D7="NA","",IF(D7>=1,1,0))

If I want to give question two a score of '3' i'm using the formula =IF(D7="NA","",IF(D7>=1,3,0))

etc. etc.

Construct a 2-column table consisting of questions and scores that indicate what they are worth when the answers are yes. Do a lookup to get the asscociated score when a question has been answered with YES. You can do any computation you desire on the scores you fetched with lookup.

 

[sclayden]

OK - In reality there is a total of 39 questions on my form. The potential total number of points that can be scored if all the questions are marked 'YES' is 103.

My calculations are based on points, rather than 30 answered 'YES' out of 39.

 

[Aladin Akyurek]

OK - In reality there is a total of 39 questions on my form. The potential total number of points that can be scored if all the questions are marked 'YES' is 103.

My calculations are based on points, rather than 30 answered 'YES' out of 39.

Read my last reply. That answers a fundamental issue Brian has rised.

 

[SIXTH SENSE]

Ok!
There are 39 questions =103 points
How are the points distributed?
it is at random or are there any guidelines to come up with
this distibution!
CAre to post your sheet?

 

[sclayden]

Lookup is a manual process, is there no way of automating it?

 

[SIXTH SENSE]

Thanks Sixth Sense

I have a question about the potential scores in column E on your example.

You've answered cell 10 as 'not applicable'. If the original value of the question in cell 10 was '4' if answered 'YES' how do you get it to change to '0' if answered 'not applicable'?

hi sclayden
The potential score was manually calculated.
In that post, if you look at the sumproduct, it
wont affect any score given to the NA. the sumproduct will only
look for those with answers = "yes" and "No" other than this, it will ignore!

 

[Aladin Akyurek]

Lookup is a manual process, is there no way of automating it?

Huh? Since when?

 

[sclayden]

Sixth Sense

My questions are becoming more and more basic! How do I post my sheet with all the formulas so you can see it?

 

[SIXTH SENSE]

hi!
This must be what you are looking for. A thing that you may see visually.
Sumproduct ignore everything that is not "yes" or "no" including NA

Book1
	A	B	C	D	E
1	Answer		WeightedAverage		Score
2	yes	1	63.16%	Automatic	1
3	yes	2			2
4	no	0			4
5	yes	3			3
6	yes	4			4
7	no	0			2
8	yes	1			1
9	yes	1			1
10	not?applicable	0			100
11	no	0			1
12	totalscore	12			19
13			WeightedAverage	63.16%
Sheet3