MS Excel in a Lab Setting

Excel Formulas: Scalar Functions

2007-06-01T10:28:00.000-07:00

Excel has more than 300 functions you can invoke in your formulas, that do a great variety of calculations and other tasks: trigonometric functions, Bessel functions, statistics, string operations, date & time conversions, and table lookups, for example. Most of these take one or more input values (cell addresses or range addresses) and produce one output. A later post will examine some that produce multiple results.

Here, we introduce the Insert Function dialog. The Menu way to get it is Insert/Function..., but the easier way is to click the fx button right next to the Formula Bar in Excel 2002 and later.

Doing so opens up this dialog box. The exact locations of the elements depend on the Excel version. This is Excel 2003.

The "select a category" dropdown has quite a list of sections; there are eight initially, plus "All" and "Most Recently Used". The last category, below "User Defined" is "Engineering", and only shows up if you have the Data Analysis Toolpak enabled. The category "User Defined" only shows up if you have a VBA Add-in containing functions of your own—a more advanced skill!

In the "Financial" category, this example will show how the PMT function can be used to calculate a mortgage payment.

This shows the formula almost finished, just before adding the closing parenthesis. C5 is the monthly Rate (cell C5 contains the formula =C4/12), C6 is the number of months, and C3 is the principal, called pv for Present Value; it is negative because a loan is a liability, not an asset. The fv (Future Value) and type (Pre- or post-payment) parameters are optional, and we don't use them here.

Upon adding the closing paren and pressing Enter, we find the payment calculated. Excel took care of using a Currency format.

Such a mini-worksheet lets you test scenarios, for example different rates or terms (e.g. 15y or 180mo) .

Excel Formulas are Algebra, part 5

2007-05-25T06:35:00.000-07:00

If you are already familiar with Excel formulas, you don't need to bother with this post or its companions of similar title.

Now we look at relative and absolute addresses. In Part 2, we saw that when a formula is copied, cell references are adjusted in a common-sense way: If you copy a formula from B3 to B4, that refers to A3, the copy will refer to A4. Also, a copy put into H9 will then refer to G9. This is Relative Addressing.

If you have a constant factor to use in a formula, in addition to other factors to be referred to in order, AND you keep that factor in a cell, you want to be able to copy the formula, but you don't want the reference to the constant to change. For example, in a financial scenario, you might want to test it with different inflation rates. Here, prices change by a fixed amount each year. You enter the initial price in B1 and the yearly percent in B2. The years of interest are in A4 to A9. Cell B4 has the simple formula =B1. Cell B5 has the formula of interest, shown here:

Notice how, when you are editing a formula, cell references are highlighed and their locations indicated by a sequence of colors. Here, blue B4 refers to the cell just above, and green B2—but here it is $B$2—refers to the inflation percent (The other calculations convert the 3 to .03 and add it to 1).

The dollar signs in the cell name indicate Absolute Addressing. When entering a formula, you can type the cell name or click on a cell; either way, at that point, if you press the F4 function key, it changes the addressing type. In sequence, you would see

B2
$B$2
B$2
$B2
B2

and so forth. $B$2 means completely Absolute; the other two make only the Row or the Column absolute, while allowing the other to be relative. This allows for creative building of tables, a topic for much later consideration.

Excel Formulas are Algebra, part 4

2007-05-23T11:59:00.000-07:00

If you are already familiar with Excel formulas, you don't need to bother with this post or its companions of similar title.

One soon runs out of uses for formulas of the type =D8+23.3. While solving algebra problems with lots of variables is tedious (and fraught with errors), this is where Excel shines. Formulas can be quite complex. This simple example just cracks the door open:

Data exist in columns A, B, C, and D, and we want column E to contain the sum of an A times a B, plus a C times a D. Of course (!) there are column labels in row 1, so in cell E2 you can put the formula

=A2*B2+C2*D2

If you are paranoid about the order things are done (multiplies get done before adds, but in larger formulas it gets less clear), you could write that formula as

=(A2*B2)+(C2*D2)

Now, suppose you want the sum of the four items, rather than this mixed sum/product formula, just A+B+C+D. This brings up the subject of Functions. These are named operations that Excel can do, and many work on Ranges. Instead of

=A2+B2+C2+D2

you could have

=SUM(A2:D2)

A2:D2 is the Range name for those four cells. Many formulas take 2-dimensional ranges, so the sum of quite a large area of the worksheet may look like this:

=SUM(A2:Z41)

That's the sum of 1,040 items.

Excel has hundreds of formulas, and the Help can provide lists and explanations. Just one more is worth mentioning, because it is used a lot:

=SQRT(D8)

This will find the square root of any numeric value in cell D8.

Excel Formulas are Algebra, part 3

2007-05-22T11:22:00.000-07:00

If you are already familiar with Excel formulas, you don't need to bother with this post or its companions of similar title.

Introduction to Formula Syntax: Excel formulas are adapted from the BASIC programming language, which has its roots in FORTRAN and COBOL. A formula just describes an operation, or a series of operations. If you decode the word Mathematics, its root is "operate". The symbols used in BASIC statements and in Excel formulas solve the problem of telling the computer what you want to do. So, here are the five fundamental "verbs":

+ means add
- means subtract (you knew these two)
* means multiply (because X and x are letters)
/ means divide (rather than ÷, which is a "special character")
^ means raise to a power (I'll explain shortly)

All other operations you might need, like a square root or some trigonometry, are handled by Functions. Those come later.

Let me first explain powers. They are like multiplication, taken to another level. Just like multiplying is a short way of performing repeated addition (5*3 = 5+5+5 = 15), taking a power is a short way of describing repeated multiplication (5^3 = 5*5*5 = 125). The second and third powers come into daily life so frequently that we have names for them, the square and the cube. Thus, most folks know that areas are measured in square feet, square yards and square meters, while volumes are measured in cubic feet or whatever.

As mentioned in the former post, a simple multiplication formula looks a lot like algebra. So this algebraic formula:

y=x*1.04

might look like this in an Excel cell:

=C4*1.04

Where did "x" and "y" go? "y" is the cell the formula is in and "x" is the column/row name of a cell you're telling Excel to operate on. Every cell in a workbook has a name, which is the column and row in which it is located. JB47650 is a cell very far from the "home cell" A1. The last column is IV, the 256th column, and the last row is 65536 (multiply these numbers, and you'll see that an Excel worksheet can contain 16,777,216 cells...for now).

Therefore =C4*1.04 tells Excel "Get a number from cell C4 and multiply it by 1.04; show the result in place of this formula." When you look at the worksheet, you just see the result. To see the formula, click on (select) the cell. Its literal contents are shown in the Formula Window near the top of the screen.

Excel Formulas are Algebra, part 2

2007-05-21T12:02:00.001-07:00

If you are already familiar with Excel formulas, you don't need to bother with this post or its companions of similar title.

What happens to the cell names (column/row names) when you copy a formula? Excel does a common-sense thing by making the references "relative". If you have a formula that refers to cell E2, and you copy that formula to the next cell below, the new copy refers to cell E3. Thus, the relative position of the formula and the cell(s) to which it refers is retained.

If the formula shown here is copied to many cells down column F, you'll get a series of formulas like this:

=E2*1.04 [the original formula]
=E3*1.04
=E4*1.04
=E5*1.04

and so forth. Sometimes more comples formulas require a cell reference that doesn't get changed in this way in a copy. We'll cover those later on, in a section on "Absolute references".

Excel Formulas are Algebra, part 1

2007-05-18T11:55:00.000-07:00

If you are already familiar with Excel formulas, you don't need to bother with this post or its companions of similar title.

Some who are new to Excel either don't know it has formulas, or thinks they are hard, particularly if they think they aren't good at math. Some fear the word "Algebra", and remember 7th or 8th grade torture sessions. However, let's take a tiny quiz. Here are two simple algebra problems:

1) x = y + 3; y = 2; what is x?

2) z = x*1.1; x = 30; what is z?

Did you answer "5" and "33"? If so, you did a little algebra.

Now, suppose you are faced with a column of numbers, perhaps a few hundred. Maybe they are prices of labware. You need to add 4% to each. Adding 4% is the same as multiplying a number by 1.04; now look at this image:

This little formula will take whatever is in cell E2 and multiply by 1.04.

Once you press the Enter key (or click on a different cell), you'll see something like this:

Now, what you do is move back to cell F2, and double-click on the tiny black square to the lower right. That is the Fill Handle. The formula will be put in the entire F column, until it runs out of numbers in the E column.

You may have a question at this point, about what happens to E2 in the formula, which I'll address in the next post.

First, let us close by relating this formula to algebra.

A statement like y=x*1.04 means "whatever 'x' is, multiply it by 1.04, and that is the new value of 'y'."
An Excel formula like =E2*1.04 means "whatever is in cell E2, multiply it by 1.04, and that is the new value to put in this cell (the one containing the formula)."

Navigation Shortcuts

2007-05-17T09:44:00.000-07:00

Once a worksheet gets more data in it than you can see without scrolling (almost immediately!), you need ways of getting around quickly. Here are some more CTRL key combinations for getting around.

CTRL plus an Arrow Key (←↑→↓) jumps in that direction to the first non-empty cell if the current cell is empty, or the last non-empty cell if the current cell is not empty.
CTRL plus Home key jumps to cell A1 or the top-left cell in the active pane if Freeze Panes is on.
CTRL plus End jumps to the cell in the last row with data, and in the last column with data (that cell may itself be empty); sometimes the pointers get stuck, and you have to save the workbook to reset them.

These are included in the lists at these links (repeated from the prior post):
One of the free offerings at ASAP Utilities is their list of a great many keyboard shortcuts.

And of course Microsoft publishes this list of all possible shortcuts, including ALT sequences for accessing all menu items.

Keyboard Shortcuts

2007-05-16T11:46:00.000-07:00

Excel is extensively menu and right-click menu driven. However, everything found on a menu can be done by keyboard. This is particularly helpful when your mousing hand begins to suffer from carpal tunnel or deQuervain's tendonitis. I find certain shortcuts also quicker, and I've reached a balance between mousing and keying with the shortcuts below. At the bottom of the post you'll find links to two long lists of shortcuts.

With one exception, the ones I'll list are all CTRL plus a key. For the CTRL key plus the A key, you might see CTRL/A, CTRL+A, or CtrlA in various tutorials. For this list, cA means CTRL plus the A key.

c1 = Format cells dialog
cA = Select All
cB = Bold
cC = Copy selected cells
cF = Find
cG = GoTo dialog
cH = Find & Replace
cI = Italic
cV = Paste (The ENTER key pastes and empties the clipboard)
c~ (CTRL plus tilde) = reset format to General format
F2 = Edit mode (toggles Edit and Enter modes)

One of the free offerings at ASAP Utilities is their list of a great many keyboard shortcuts.

And of course Microsoft publishes this list of all possible shortcuts, including ALT sequences for accessing all menu items.

For beginners, rank & not so rank

2007-05-15T11:38:00.000-07:00

The scientists and technicians at my workplace use MS Excel (several versions) as their default lab notebook. They vary greatly in their ability to analyze and summarize their data once it is gathered. I get "Can Excel do such-and-so?" questions frequently.

I'll gather useful techniques and tips in these posts. For those who are really new to using Excel, I recommend the following online tutorials:

GCF Global Learning®: Free tutorials in six areas, including Office 97, 2000, 2002(XP), and 2003. The Excel 2003 section has 27 modules. Free membership registration required.

Microsoft Online Training: Office 2003 and 2007. The Excel 2003 section has 38 courses, each with a few dozen modules.

You will also benefit from a book or two. I have three of the four listed below. Were I starting over, I'd get the "dummies" book first:

Excel for Dummies by Greg Harvey, PhD: http://www.dummies.com/WileyCDA/DummiesTitle/productCd-1568840500.html

Excel 2003 Bible and Excel 2007 Bible by John Walkenbach: http://www.amazon.com/exec/obidos/search-handle-url/index=stripbooks&field-keywords=excel%20bible&results-process=default&dispatch=search/ref=pd_sl_aw_tops-1_stripbooks_4245308_2&results-process=default

Excel for Chemists by E. Joseph Billo, PhD: http://www.amazon.com/Excel-Chemists-Comprehensive-Guide-2nd/dp/0471394629

Excel for Scientists & Engineers by Gerard Verschuuren: http://www.amazon.com/Excel-Scientists-Engineers-Professionals/dp/193280210X