Formulas: From Similac to Soylent Green

The Basics:

What is a formula? Simply, a formula determines what will appear in a cell based on the information you specify. How do I specify this information? Well, in Excel, you enter this information by placing an equal sign in your destination cell and entering the formula after this sign. In Lotus 1-2-3, you substitute a + or an @ symbol to enter a formula, depending on the situation.

Mathematically speaking, we all learned the basic four numeric formulas in elementary school: add, subtract, multiply, and divide. From these four functions, a whole world of calculation is possible. These are the four symbols with which you use with the + symbol to enter a formula in 1-2-3.

Or, since you already have the amounts in cells A2 and A3, you can use cell references instead of the actual numbers.

Sure that works for the simple formulas, but what about a range of cells? Well, here we break off into actual functions. In 1-2-3, you will need to use an @ symbol before each function you are using. The most commonly used is SUM, which adds a range of cells.

You will notice that a range of cells is listed is separated by a character or series of characters. In Excel, you use a colon to include all cells in a range, while 1-2-3 uses two dots. In either program, a comma can be used to add in a number or cell reference that is not connected to the other cell or cell range in your formula.

Another commonly used function is the "IF" statement. IF is used to set conditions. An IF statement consists of three parts: Condition, True action, False action. The condition is what you are checking for, the True action is what you would like to happen if your condition is met, and the False action is what you wish to do if this is not met.

This is great if you only have one item you are checking for, but what if there are more qualifiers than just one? That is where a "nested if" comes in handy. A nested if statement consists of an if statement within another if statement. "If this the first statement is true, then check to see if the next statement is true and return value 1 or if it is false and return value 2. If the first statement was false, do not check the second if statement, just return value 3."

A nested if statement can be confusing, so you need to track which condition is being checked on at all times while troubleshooting this type of formula. You might also notice that the @ symbol is used before each function (in this case, the IF function) in Lotus 1-2-3. This is not necessary in Excel.

Now that we’ve established the fundamental basics and can see how a formula is constructed, let’s get into the wonderful world of functions. If you haven’t seen Excel version 5.0 or greater, you’re missing one of the greatest tools in spreadsheet history. If you HAVE seen it, you will probably notice a small button, unobtrusive button which looks like . This button is the Function Wizard, and will become your best friend in the world of functions.

The Function Wizard will allow you to build a formula by selecting a blank cell, then clicking the Function wizard button and selecting the function you wish to use. Clicking the Next button will take you to the parameters screen, which allows you to enter your cell references, your qualifiers, or just get help using this function by pressing the f1 key or the Help button.

This page hosted by Get your own Free Home Page