In Excel (or OpenOffice Calc), suppose you want to have a good old fashioned if elseif chain. or even just a bunch of fall-through ifs.
Like if we have a table:
| id | school name | school type |
| 4517 | Coronado Elementary School | ? |
| 4710 | St Johns Middle School | ? |
| 4711 | St Johns High School | ? |
| 4712 | Window Rock Elementary School | ? |
| 4716 | Tsehootsooi Middle School | ? |
| 4717 | Window Rock High School | ? |
...and we want the "school type" field to be programmatically based on the contents of the "school name" field. So if the name contains "Elem", make the type "Elementary", and so forth.
We could do that in VBA no problem, but sometimes you don't want to deal with that. Like telling your mom in an email how to copypasta the VBA code, and how she won't understand the "OMG Macros!!!1!" warning every time. Besides, VBA won't work so well in OpenOffice Calc, which you tried to convince your mom would be just as good.
You CAN actually do this in a cell. It is just kinda ugly. Actually, the IF() isn't so bad. The chaining/fall-through thing is what gets rough.
=IF( NOT( ISERROR( SEARCH("Elem",B2) ) ),"Elementary" , "Something Else" )
At the heart of this is the SEARCH() function, it either returns the index in the string if it finds it, or it shits the bed if it doesn't. It just errors out and screws the whole thing.
ISERROR() will catch the error and turn that frown upside down, returning a simple TRUE or FALSE boolean. Thing is, we need a !ISERROR(), hence the NOT()
Finally, the IF(), which will put "Elementary" if search was NOT an error.
Thing is, if SEARCH() IS an error, you can have something else put in... and by something else I mean you can in-line a function call!
=IF(NOT(ISERROR(SEARCH("Elem",B3))),"Elementary",
IF(NOT(ISERROR(SEARCH("Middle",B3))),"Middle", "Something else") )
this line was broken for web-page sake, but is all one formula in Excel.
So now, we have an IF() looking to see if SEARCH() for Middle throws an error, or not to return the value to stick in if the Elementary SEARCH() fails.
=IF(NOT(ISERROR(SEARCH("Elem",B2))),"Elementary",
IF(NOT(ISERROR(SEARCH("Middle",B2))),"Middle",
IF(NOT(ISERROR(SEARCH("High",B2))),"High", "Something Else" ) ) )
Again, all a one line formula. Supposedly you can go 7 deep with this, but I haven't tried it because 7 ought to be enough for anybody.
Hope that helps. If it did, how about a +1 or a like?
