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.