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

sclayden

New Member
Joined
Feb 19, 2004
Messages
23
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!!)
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

sclayden

New Member
Joined
Feb 19, 2004
Messages
23
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

Well-known Member
Joined
Jan 9, 2003
Messages
632
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

Maybe?
Book1
ABCD
1yes66.67%
2yes
3no
4yes
5yes
6no
7yes
8yes
9notapplicable
10no
Sheet1


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

sclayden

New Member
Joined
Feb 19, 2004
Messages
23
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458

ADVERTISEMENT

sclayden said:
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

New Member
Joined
Feb 19, 2004
Messages
23
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

Well-known Member
Joined
Oct 29, 2003
Messages
1,883
Hi!
how about a sumproduct.

see below if this is the proccess you want.
Book1
ABCDE
1AnswerWeightedAverageScore
2yes163.16%1
3yes22
4no04
5yes33
6yes44
7no02
8yes11
9yes11
10not?applicable00
11no01
12totalscore12Potentialscore19
13ManualWeightedAverage63.16%
Sheet3
 

sclayden

New Member
Joined
Feb 19, 2004
Messages
23
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'?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top