Barker's Code

Following Suit with Sisi, I thought it would be best to provide an example that works with text rather than numbers.

As we have said a number of times now, strings are often more difficult to work with, and yet they are often more intuitive than if you were to use a series of numbers to describe your data. For instance, I could describe two groups of rats as "0" and "1" or "low-dose" and "high-dose". To an individual than knows the code, both are essentially the same. However, to the naive onlooker, the amount of information provided by the second example is far greater.

Learning to work with strings is a powerful tool for data analysis. Moreover, as we continue to work with Matlab and Excel, you will likely realize that Matlab is not as "string-friendly" as excel, and that tasks involving strings are often better accomplished in excel. In fact, we will often use excel to convert strings to numbers before importing the data into Matlab.

The nested code I have written for you is as follows:

'=IF(AND(ISTEXT(A5),ISTEXT(B5)),CONCATENATE(A5," ",B5),"ERROR!")'

Lets break this down into a few pieces.

1) The deepest layer of this function is the "ISTEXT" command. Like many logical commands in excel this is designed to return either TRUE or FALSE, depending on whether or not a given criterion has been met.

In this case, ISTEXT is simply asking "Is the content of the referenced cell text?" and returning TRUE if the answer is YES, and FALSE if the answer is NO.

2) The two instances of ISTEXT in the above formula are embedded within the "AND" function. This is another logical function that returns TRUE if both values inside of the function are TRUE, and FALSE if either one, or both of the functions are FALSE.

To give you a more colloquial example, the function (in my formula) is asking "Was the answer to BOTH the first AND second intext command TRUE?".

3) While the "IF" function is the outermost layer of my nested function, it needs to be explained before the last function in order to make the whole thing easier to comprehend.

The IF function has 3 inputs (i.e."=IF(INPUT1, INPUT2, INPUT3)").

The first input is your logical criterion. In my example, the logical criteria is the the AND function described above. Thus, if both cells A5 and B5 contain text, the value would be "TRUE" and the criterion has been met.

The second input is the value you want the function to return when the function is TRUE. In this case, I have designated that the concatenate function be called when the function is true. The result of this function is described below.

The last output is the value to return when the criterion has NOT been met (i.e. is FALSE).

For example, if I created the function "=IF(5=4,"TRUE","FALSE")", the formula would return the string FALSE, because the numbers 5 and 4 are not equal. However if I modified the formula to "=IF(5=5,"TRUE","FALSE")", the string TRUE would be returned.

Returning to the main example, the value I have designated if cells A5 and B5 are NOT both strings is "ERROR!".

4)Finally, the fourth function in my nested function is the "CONCATENATE" function. This function is used to combine strings together. Therefore, when the criterion for the IF function is TRUE, the CONCATENATE function combines the strings in A5 and B5. To make things prettier I have even inserted a space by concatenating A5," ", and B5. As you can probably see, the middle string is nothing more than a space.

Sisi's Code and Some Tips

Hi, Guys,

This is Sisi. I am posting my code, "The Awesome Detector" (Find a specific string, if the string is detected return one text, if not return another text).

Generally speaking, when I am making a program, I always ask myself three questions:

1, What information/data do I have at hand? i.e. What are my inputs?

2, What do I want from those information/data? i.e. What should be my outputs?

3, How can I get from the inputs to the outputs?

The answer to the first question sets your current location, the answer to the second question sets your goal/destination. The answer to the third question defines the route connecting the current location to the destination, which in most cases, is not unique. It is nice if we can find a fast (require less time) and cheap (less computing resource involved) way, but for most things we do in the lab, I would say, as long as your can get there!

Answering the third question require some thoughts. You can either go from the inputs, gradually approaching the outputs; or, start from outputs, and ask what do I need to get there, and back track to the inputs; or, starts from both ends, and hope to meet in the middle...

Take the following task as an example (essentially a text finding task ):

Inputs: sentences. Excel consider this type of input as the format of text (in other programs, they can be defined as string format or character format). The format of your input is important, since it restrict the operation you can perform. We will keep coming back to this point later.

Output: There are two possible outputs: (1) Awesome. If "sisi" appears in the input (2) :(. If "sisi" does not appear in the input. This is way I call this program "The Awesome Detector"

Algorithm (route connecting Input and Output) :

(1) Make the input upper case. We will search for the word "sisi" in the input, since most of the text search functions are case sensitive, we force the input to be uppercase to make the search step easier. function Upper is used.

When you Click enter, SISI LOVE CANDY (should be loves, excuse the foreigner), will appear in A5.

(2) Check if "SISI" appears in the converted input string. I used find function as following: =FIND("SISI",C5,1), when click enter output will showup as 1, indicating the text "SISI LOVE CANDY" matches "SISI" from the first position.

(3) get rid of some bugs,the find function will return "#VALUE!" error, if "SISI" did not appear in the input text, which looks bad. we'll fix it using iferror function. IFERROR fuction takes the index of the cell as argument, return true if an error indeed appear, otherwise will return false as output.

In the figure on the left hand side, cell E1 contains the ISERROR function (check out the fx bar on top right), it tests if D1 contains an error, which does, so in E1 is output is TRUE. Similarly, Cell E2 contains uses function "=ISERROR(D2)". D2 contains the number 6 (which means "SISI" appears at the 6th position in C2), so E2 shows up as FALSE, meaning D2 does not contain a error.

(4) Final move. In the figure above Column A contains the inputs (one per row), Column C makes text in Column A in to upper case by UPPER function (in the corresponding row, same in the following columns). Column D test if "SISI" appears in column C, return the position of "SISI" if it appears, return "#VALUE!" if it does not appear. Column E check if the corresponding row of Column D contains error, if have error return TRUE, otherwise return FALSE. Finally, remember the goal, we want to return AWESOME if "SISI" appears in the input (in this situation column E should have the value FALSE). We can simply use a IF function : =IF(E1,":(","AWESOME"). As shown in the following figure. the first argument in if function is a logical value (TRUE or FALSE), second argument is the output when the logic value is true, third argument is the output when the logic value is false. Therefore in our case F1 returns :(, since E1 is TRUE, whereas F2 returns AWESOME since E2 is FALSE.

Packaging: So, we completed our goal in the above 4 steps. Now, it is the time to package the process into a nice compact form, using function nesting as the following: =IF(ISERROR(FIND("SISI",UPPER(A1),1)),":(","AWESOME"), A1 is the cell where the input is written.

To summarize, getting output step by step makes trouble shooting easier, however, packing using nested functions gives you compact results and a clearer spreadsheet.