Writing Formulas
You use formulas to perform mathematical functions on cells. There are two basic ways of writing formulas available: “ranged syntax” or “simple syntax.” A “syntax” is simply a way of expressing or writing something. It is important to note that these two syntaxes are not mutually exclusive! In fact, your more complex formulas will often incorporate elements of both in order to arrive at the desired result. Typically, you will use the simple syntax to perform multiple mathematical calculations on multiple cells. You use the ranged syntax to perform a single mathematical function over multiple cells.
To write a formula, first click into the cell where you want the results of the formula to appear. Next, write your formula. When you are finished, exit the cell to display the answer to the formula that you wrote. Once again, this is the concept of “content versus display” at wok. The actual content of the cell will be the formula, but the cell will display the answer to the formula. If you click into the formula cell after it has been created, you will see the actual formula displayed in the Formula Bar and the answer displayed in the worksheet. To show the actual formulas in the worksheet, you can press the “Ctrl” key and the ` (single left quotation mark) key. Each time your do this, Excel will toggle the worksheet display between the answers and the formula.
When creating ranged syntax formulas, first select the cell into which you wish to enter the formula. Start by typing the equal sign. Formulas always start with an equal sign (=). This prevents Excel from interpreting the formula as a simple text entry- since formulas are just letter/number combinations. Next, type the name of the function which you wish to perform on the cell range or cell ranges. Then type an open parenthesis. Next, input the cell range or cell ranges upon which you wish to perform the function. Finally, type a closed parenthesis. Then exit the cell using your keyboard, or click the green checkmark button in the Formula Bar, to set the formula. Note that you don’t use spaces between elements in the formula. However, they are not case sensitive.
Creating a simple formula is like writing a standard math problem. Once again, you start by selecting the cell where you want to enter the formula. Type an equal sign (=). Next, type the cell addresses that you want to use in the formula joined together with the standard mathematical operators. You can also enter standard numbers into a formula, if desired. However, you will more often want to manipulate the values of the cells (which may change) and thus you will more commonly use cell references, instead. If you want to perform a single calculation on a range of cells, it is usually easier to create a ranged formula instead of a simple formula. In simple formulas, the standard order of operations applies: designated operations are performed from left to right with anything in parenthesis calculated first, then exponentiation, then multiplication and division, and finally addition and subtraction.
Note that you can also use both syntaxes within a single formula. For example, if you wanted to sum the first ten cells in column A and then subtract from that value the sum of the first ten cells in column B, you could express it as a single combined formula such as: =SUM(A1:A10)-SUM(B1:B10). In this case, you are using a simple syntax formula to subtract the values derived from two ranged syntax formulas. Most of the more complex formulas that you create will incorporate both syntaxes in order to derive the answer.
Formulas that use cell references will automatically be recalculated when you enter or change the values in the cells referenced by the formula. This is one of the best features of Excel, and one of the reasons that you will rarely find numbers entered into a formula.
When you use cell addresses in formulas you can either type them, or use the mouse to select the cell or cell ranges that you want to insert. As always, once you are finished writing the formula, leave the cell in order to save the formula and display the answer.
Check out these two videos on Ranged Formula Syntax and Simple Formula Syntax:
