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.

Dave's Code

 

Microsoft Excel Basics for Data Analysis:

First and foremost, it should be said that the current tutorial is meant to provide instructions that allow an individual to conduct basic data analysis. Most of the instructions are specifically tailored to this purpose. Excel tutorials for a wide range of topics can be found through Microsoft:

http://office.microsoft.com/en-us/excel/

http://office.microsoft.com/en-us/excel-help/

I would encourage anyone who wants to get better at excel to sit down with a few of these tutorials.

Microsoft excel is broken down into rows and columns. Rows are horizontal and designated by numbers, while columns are vertical and represented by letters. Together these create a coordinate system that allows you to reference a single cell or group of cells. For example the selected cell in the screenshot above would be cell “J9”. You can think of these coordinates like a game of battleship.

A cell is represented by a single box (Single coordinate) and can contain multiple types of data: numbers, strings, or functions.
A number is any numeric entity- e.g. zero, negative, positive, fraction, etc.
A string is a series or characters or symbols chosen from a given alphabet. While a string can contain numeric entities, it can also contain letters and other symbols. For example, “AWESOME” and “AW3$0ME” would both be examples of strings.


A function uses either mathematical formulas or logic to produce an output/return. In excel these can be mathematical equations (e.g. “=2+4”), but many are also designated by a string (e.g. “=ABS( )” to calculate the absolute value of a given input; “ABS” being the string). In essence, both are calling some basic mathematical function, logical function, or a combination of the two. Note: The inputs to a function are called arguments/inputs, while the value(s) returned by a function are called outputs/returns.

A complete list of Excel functions/formulas can be found by clicking the fx button. Mathematical functions/formulas follow standard notation (+,-,*,/,^) and order of operations.

You should also note that the blank section to the right of the fx symbol is used for editing functions/formulas. Alternatively, functions/formulas can also be edited by typing directly into a cell. In fact, newer versions of excel have pop-up windows to help you remember the names of functions/formulas and enter them correctly(see below). This window even provides a brief description of the selected function.

While numbers and strings can be directly inserted into a cell, functions/formulas always begin with an equals sign “=”. Microsoft excel will also tell you what values should be inserted into functions/formulas.

To put it another way, when considering numbers and strings, “what you see is what you get”. On the other hand, the value that you see for a cell with a function is the final output or returned value of that function/equation.

Note that in a functions/formula, the inputs can either be directly entered (e.g. =2+4) or they can be references to other cells. For example, cells B2 (column B, row 2) and C2 (column C, Row 2) are being referenced and added together in the above example for “Math”. Alternatively, a series of cells can be referenced by indicating the range of cells. This can be seen in “Excel functions” where cells B4:C4 (read B4 through C4) are being referenced. Finally, a list of discontinuous cells can be inserted into a function/formula by using a comma separator (e.g. “=AVERAGE(C5,D7,J22)”).
Finally, functions can be nested within other functions. There can be many layers to this “nesting”, but a good rule of thumb is to avoid nesting more than 7 functions at once. The limit to the number of functions that one can nest depends on the version of Excel.

An example of nesting functions would be to find the average of a series of negative numbers (e.g. -24, -25, and -26, the average of which is -25) and then take the absolute value of the average(+25). This could be accomplished by nesting the AVERAGE function within the ABS function.

=ABS(AVERAGE(-24,-25,-26))

Note: When nesting functions, only the first function is preceded by an equals sign.



On some occasions, it becomes necessary to fix a row, column, or both in a formula. This can be accomplished by “pinning”. This is accomplished by using the “$” before either the row number, column letter, or both.
For example, if I wanted to multiply all of the cells in the second row by a value in A1 (e.g. the drug dose of an infusion), I could used a fixed reference for A1 and a moving reference for values in the second row. In Microsoft excel, the fixed references are known as “absolute references”, while the moving references are known as “Relative references”.

Finally, you may find an instance where you need to reference another worksheet. This is accomplished by referencing the worksheet name, followed by “!”, and then the cell(s) coordinate(s) you want to reference in that sheet.

E.g. “=ABS(Sheet1!A9)”


WEEKLY CHALLENGE- CREATE A NESTED FUNCTION THAT CONTAINS A TOTAL OF FOUR FUNCTIONS
e.g. =Function(Function(Function(Function)))

We will begin working with Matlab in the weeks to come. For those of you that are interested in learning more about Matlab, I recommend two books:

For beginners:


More than you could ask for: