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!!)

 

[Aladin Akyurek]

Wouldn't

=COUNTIF(RangeOfAnswers,"YES")

suffice?

 

[sclayden]

Thanks for the quick response!

I'm not sure where I would need to put this formula?

Could I send you my Excel form to look at?

 

[ken2step]

What if you put the formula
=IF(B2="Not Applicable","",IF(B2="Y",1,0))

next to the answer column to evlaluate the answers, then put this formula
=AVERAGE(C2:C11)
anywhere you want the percentage.
By entering the "" for Not Applicable it is not used in the Average function when evaluated.
Ken

 

[Brian from Maui]

Maybe?

Book1
	A	B	C	D
1	yes		66.67%
2	yes
3	no
4	yes
5	yes
6	no
7	yes
8	yes
9	notapplicable
10	no
Sheet1

Not sure how you're using =IF(D7>=1,1,0) however.

 

[sclayden]

I've used Ken2Step's formula to solve my 'not applicable' problem, however I still have a problem with calculating the potential score and actual score.

The numerical value given if a question is marked 'YES' is not always 1. Each question can have a set value of between 1 and 4 (e.g. question one could have a value of 1, question two of 3, question three of 2, etc.)

This means that the AVERAGE formula will not work.

Any suggestions?

 

[Brian from Maui]

I've used Ken2Step's formula to solve my 'not applicable' problem, however I still have a problem with calculating the potential score and actual score.

The numerical value given if a question is marked 'YES' is not always 1. Each question can have a set value of between 1 and 4 (e.g. question one could have a value of 1, question two of 3, question three of 2, etc.)

This means that the AVERAGE formula will not work.

Any suggestions?

How would Excel know what value to give a yes answer?

 

[sclayden]

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.

 

[SIXTH SENSE]

Hi!
how about a sumproduct.

see below if this is the proccess you want.

Book1
	A	B	C	D	E
1	Answer		WeightedAverage		Score
2	yes	1	63.16%		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			0
11	no	0			1
12	totalscore	12		Potentialscore	19
13		Manual	WeightedAverage	63.16%
Sheet3

 

[sclayden]

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'?