Converting Formula Yes No 0 1

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Mar 28, 2014

• #2

Try

=--(INDEX('Sheet2'!$AS$20:$AS$100,MATCH($BK20,'Sheet2'!$A$20:$A$100,0))="No")

If the INDEX = Yes, it will return 0
If the INDEX = No, it will return 1

 

Upvote 0

M

Mstg007

Active Member

Joined

Dec 30, 2013

Messages

383

• Mar 28, 2014

• #3

Thank you. Could I ask what if I wanted to change the values from NO to 5 and YES to 4?

 

Upvote 0

M

Mstg007

Active Member

Joined

Dec 30, 2013

Messages

383

• Mar 28, 2014

• #4

Sorry for got one thing. Could I get R to 3?

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Mar 28, 2014

• #5

You're welcome.

Try
=(INDEX('Sheet2'!$AS$20:$AS$100,MATCH($BK20,'Sheet2'!$A$20:$A$100,0))="No")+4

 

Upvote 0

M

Mstg007

Active Member

Joined

Dec 30, 2013

Messages

383

• Mar 28, 2014

• #6

Thank you so much again! Don't worry about the R thing... I can work around that. Is there also a way to reverse it? So Numbers to Letters?

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Mar 28, 2014

• #7

Why don't you explain what you really want, in it's Entirety.
Instead of adding on little bits and pieces as we go.

 

Upvote 0

M

Mstg007

Active Member

Joined

Dec 30, 2013

Messages

383

• Mar 28, 2014

• #8

I will do my best to explain. I am trying to convert letters (Yes, No, R) to Numbers (R=3, Yes=4, No=5) and then vice versa in another formula. (So two formulas). The reason is that I am saving this out to an external file. The other program I am opening it with can only use Numbers not letters. So As I change the values in the other program to the numbers, I can import them back into my spreadsheet as Yes NO R etc.

I hope that makes sense. Thank you again

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,464

Office Version

1. 365

Platform

1. Windows

• Mar 28, 2014

• #9

Mstg007 said:

I will do my best to explain. I am trying to convert letters (Yes, No, R) to Numbers (R=3, Yes=4, No=5) and then vice versa in another formula. (So two formulas). The reason is that I am saving this out to an external file. The other program I am opening it with can only use Numbers not letters. So As I change the values in the other program to the numbers, I can import them back into my spreadsheet as Yes NO R etc.

Click to expand...

Why not leave everything in numbers and just Custom Format your cells using this...

[=3]"R";[=4]"Yes";"No"

 

Upvote 0

Jonmo1

MrExcel MVP

Joined

Oct 12, 2006

Messages

44,061

• Mar 28, 2014

• #10

OK, Sorry to sound so blunt before.
Reason being that many times the solution to your 'simplified' version is not applicable to your 'real' situation.
Which is the case here.
With just the Yes or No, it's a very simple binary 1/0 True/False function.
It get's more complicated with more than 2 possible scenarios.

Anyway, off the soapbox.


Try these
replace index with your original formula.

For Words/Letter to Number
=MATCH(index,{"R","Yes","No"},0)+2

To reverse it
=CHOOSE(A1-2,"R","Yes","No")
A1 is the cell holding the 3 4 or 5

 

Upvote 0