If statements

[Dandog]

Apologies, I'm sure this is easily answered.
I have a list of values that need converting to a numeric value -

Strongly Agree (SA),
Agree (A),
Slightly Agree (SLA),
Slightly Disagree (SLD),
Disagree (D),
Strongly Disagree (SD)

SA=6
A=5
SLA=4
SLD=3
D=2
SD=1

Can anyone advise how to do this?

Many Thanks

[NBVC]

Try in adjacent cell

=Match(A1,{"SD","D","SLD","SLA","A","SA"},0)

where A1 contains one of those codes to convert.

[zbor]

Try lookup:

=LOOKUP(A1,{"SD","D","SLD","SLA","A","SA"},{1,2,3,4,5,6})

[NBVC]

Try lookup:

=LOOKUP(A1,{"SD","D","SLD","SLA","A","SA"},{1,2,3,4,5,6})

That won't work, since first array must be in sorted ascending order.

[zbor]

Ah, yes, sorry... Writed from head

[Dandog]

Hi, Sincere apologies for the delay in replying, other things came up.

I have tried the above and could not get it to work, I think I was not that specific in my original query.

I have about 2500 rows of data to sort out. I need to convert a text field that contains 6 values -
"Rating: = Strongly agree"
"Rating: = Agree"
"Rating: = Slightly Agree"
"Rating: = Slightly disagree"
"Rating: = Disagree"
"Rating: = Stongly disagree

Into a numerric value 6,5,4,3,2,1.

I really appreciate your help.
Regards - Gareth

[DonkeyOte]

If you want to store the numeric equiv. in an adjacent column to the descriptions then all you need do is alter the strings in NBVC's prior example to reflect your actual setup, below is the original:

=Match(A1,{"SD","D","SLD","SLA","A","SA"},0)
where A1 holds your text field value

so in reality "SD" should be replaced with: "Rating: = Strongly disagree", "D" should be replaced with "Rating: = Disagree", "SLD" with "Rating: = Slightly Disagree" etc...
(if the naming conventions are truly consistent you could shorten the strings slightly by adjusting the initial lookup value but not worthwhile IMO)

[Dandog]

Hi me again, I seem to be getting errors when trying to implement the above.

I have tried -
=MATCH(B2,{"Rating: = Strongly agree","Rating: = Agree","Rating: = Slightly Agree","Rating: Slightly disagree","Rating: Disagree","Rating: Strongly Disagree},6,5,4,3,2,1)

As I am trying to convert a field that holds

Rating: = Strongly agree to a value 6
Rating: = Agree to a value 5 etc

Hope someone can make it clearer.
Thanks -

[DonkeyOte]

I suspect we could wrap this up quite quickly if you could post a sample file that reflects the values being used...

You don't specify the error you're getting but I would hazard a guess it's #N/A which if true implies B2 is none of the values listed in the inline array.

Why ? Well though they appear similar this could be down to leading/trailing spaces etc but in truth without seeing the real values being used we're guessing.

If you prefer not to upload a file you could first try encasing the reference to B2 within a TRIM, ie replace B2 with TRIM(B2) and report back.

[khamilton]

Well depending on how it is setup this seems to work for me.if statement.xlsx

[mewingkitty]

Please Login or Register  to view this content.

To go with NBVC's original suggestion.

The 0 at the end of the formula is not a reference value, it is "0" = exact match.

It will find a match and list the number of the series which corresponds to it. Your list is five items long, if it finds a match with the third item, it will return a 3.

mew!

EDIT:
I love edits.
Looking at the order you want your values to be returned, you may need to reverse the order of the list in that formula, but it should work for you, and may help you understand what the formula is doing!