0

I have a set of school results. Students either get

WT AT AB

What formula can I use to convert these into 0,1,2 so as to compare last periods results to now?

I have tried to use the IF function nested so for eg:

IF (S2=WT, "-1", IF(S2=AT, "0", IF(S2=AB, "1"))) but this is obviously incorrect.

Thanks

Improve this question
3
  • 2
    What formula have you tried to use? I have voted to close. You've shown no research effort and your question reads like it's a homework question and you just want some one to give you the answer. Instead, please edit your question and show your attempts, it makes it easier for us to see where it's gone wrong
    – Dave
    Commented Dec 16, 2016 at 8:28
  • Sorry I had the impression this forum was to help each other. Yes, I would like someone to give me the answer !
    – Rachel A
    Commented Dec 16, 2016 at 8:58
  • 2
    it's not so obvious it's incorrect, surely you need to use "s around letter scores too, ("WT"), and it doesn't have the same logic as you've described, but otherwise it seems ok. What exactly the issue is with it? (Yes, this forum is to help each other, but not in a dumb way, instead we try to really understand the issue before helping, and that requires well written questions too.)
    – Máté Juhász
    Commented Dec 16, 2016 at 9:07

3 Answers 3

Reset to default
3

The IF Function is the correct formula for this, You can achieve the result by using

=IF(A1="WT",0,IF(A1="AT",1,IF(A1="AB",2,""))))

provided that A1 is where your value is, you may then proceed to change the cell value to the corresponding cell in your sheet.

Improve this answer
3

Another way to do it would be (assuming the school result is in A1)

=(SEARCH(A1,"WTATAB")-1)/2

This works as follows: SEARCH() looks for its first argument in its second argument, and returns the position (starting at 1). Thus, for any valid value (one of WT, AT, or AB) in A1, it will return 1, 3, or 5. We then subtract 1 from that result, giving 0, 2, or 4. That happens to be exactly twice the set of values we actually want, so we divide by 2, giving 0, 1 or 2.

Improve this answer
2
  • This is very interesting solution. Just note the OP wanted -1,0,+1 values.
    – kolcinx
    Commented Dec 17, 2016 at 11:33
  • His original erroneous IF() did use -1, 0, 1, but his text asked for 0, 1, 2. To get -1, 0, 1, he'd just need to do an additional subtraction of 1 afterward: =((SEARCH(A1,"WTATAB")-1)/2)-1
    – Jeff Zeitlin
    Commented Dec 19, 2016 at 15:33
1

VLOOKUP

Yet another approach, with a bit more flexibility in long run, but more work now, is to use the VLOOKUP function.
This requires you to have a lookup table where you have in first column the values you want to look up (e.g. "WT", "AT". etc.).
In column to the right you then put values you want to "convert" them to. (Technicaly it is not conversion, it's just a lookup :) )

So you set up a lookup table for example in range A1:B4: 

Result  NumericEquivalent
WT      -1
AT       0
AB       1

Formula: =VLOOKUP(S2, $A$1:$B$4, 2, FALSE). The S2 should contain any of the WT AT AB.

Explanation and links:

MS VLOOKUP function

In its simplest form, the VLOOKUP function says:

=VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE).

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .