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: