If statements & Microsoft Excel

Cork

If statements & Microsoft Excel

If you have a list of objects that are either say black of white such as

Black Board
White Snow
Black Bird
White Album

If you want to code White as 1 & Black as 2 – What type of if statement do you use:

If (whatever cell like “White” 1, 2)

The Corinthian

If the first word is always going to be the colour then:

IF(LEFT(A1,5)="White",1,2)

kayos

Just a note that looks like it should be IIF not IF
IIF(Expression,True,False)

kayos

bonkey

If you have Black, White, or Neither to contend with, and if its not guaranteed to be the starting characters, there is still a way to do it, but its a bit more complex.

Here's the Code :

=IF(NOT(ISERROR(SEARCH("White",A1))),1,IF(NOT(ISERROR(SEARCH("Black",A1))),2,0))

Now, I could just leave you use it and see it works, but to be honest, you'd be much better off actually figuring out what it does and how it does it.

To help you to that end, here's an explanation of it. Forgive me if I'm assuming too basic a level in the explanation, but Id rather do that than have to come bakc and explain it a second time for being too short.

The basic structure is :

If  NOT Condition A return 1
ELSE ( IF NOT Condition B return 2
       ELSE Return 0
     )

Condition A says : Does searching for the word "white" generate an error? (SEARCH returns #VALUE if it fails to find what your're looking for).

If it did generate an error, then The word White is not present. If it didnt, then white *is* present, and therefore we get our first line now reading like :

If searching for "white" did not return an error, then return 1.

The second IF is exactly the same, only searching for "black".

So we now have :

If searching for "white" did not return an error, then return 1.
Otherwise, if searching for black did not return an error, then return 2.

And finally, we simply say :

Otherwise, return 0.

The trick is that Excel doesnt permit ELSIF statements, so you have to put the second IF statement as the ELSE (false) clause of the first one...if you see what I mean.

Hope that helps

jc

bonkey

Originally posted by kayos
Just a note that looks like it should be IIF not IF
IIF(Expression,True,False)

Not in Excel m8, they call it IF, and (obviously) dont have an IIF statement.

Dumb or what....

jc

kayos

Well VBA does have a IIF so I was guessing that it would carry over. I was never big into Excel except for pivot tables and OLAP and its crap for those too

kayos

Inspector Gadget

...I'd refine bonkey's method to make it case-insensitive:

=IF(NOT(ISERROR(SEARCH("WHITE",UPPER(A1)))),1,IF(NOT(ISERROR(SEARCH("BLACK",UPPER(A1)))),2,0))

(remember, it's important that the search terms are in upper case if we're searching an upper-case version of the cell contents)

Hope this helps,
Gadget

Cork

Thanks for all your help - it was much appreciated.