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

#### sclayden

##### New Member
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!!)

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Wouldn't

suffice?

#### sclayden

##### New Member
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
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

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

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

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

#### sclayden

##### New Member
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'? Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,167,817
Messages
5,855,799
Members
431,764
Latest member
scottishbigyin ### 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.

### Which adblocker are you using?    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

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