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]

Something like this

Book1
	A	B	C	D
1	question	points	answer	points
2	1	2	yes	2
3	2	3	no
4	3	4	yes	4
5	4	5	no
6	5	6	yes	6
Sheet1

Edit,

To get a percentage, sum column D and divide by the sum of colmn B

 

[SIXTH SENSE]

Hi!
Below this post, is a downloadable Addin names colo's HTMl maker.You can use it as my posts.

 

[sclayden]

I'm at work at the moment and cannot download.

Is there any way anyone can look at my form on email? It will be sent through a company firewall, so it will be virus checked.