Basic Formulas

Introduction:

Perhaps the most valuable feature of Excel is its ability to produce mathematical outputs using the data in a workbook with the construction of formulas for basic and complex mathematical computations.

A formula is an expression that calculates the value of one or more cells. The formula performs calculations using specific values in a particular order. You can create simple formulas to add, subtract, multiply, and divide the values in your worksheet. Always begin your formula with an equal sign (=), followed by numeric values or cell references where values exist and calculation operators, such as plus (+) to add, minus (-) to subtract, asterisk (*) to multiply, and forward slash (/) to divide.

Learning:

Formulas are used to calculate a variety of mathematical outputs in Excel and can be used to create virtually any custom calculation required for your objective. Furthermore, when constructing a formula in Excel, you use cell locations that, when added to a formula, become cell references. This means that Excel uses, or references, the number entered into the cell location when calculating a mathematical output. As a result, when the numbers in the cell references are changed, Excel automatically produces a new output.

How to Enter a Formula

To enter a formula, perform the following steps:

• Select the cell where you want your calculated value to appear.
• Type an equal sign (=). When the first character entered into a cell location is an equal sign, it signals Excel to perform a calculation or produce a logical output.
• Build your formula using a combination of cell references, numeric values, and mathematical operators to create virtually any custom calculation required for your objective.

For example: You want to automatically calculate the addition of the values that are in cells B1, B2, and B3, and you want the answer to appear in cell B4.

• Select cell B4
• Start your formula with = to signal Excel that a formula is being entered.
• Type B1. This adds B1 to the formula, which is now a cell reference. Excel will use whatever value is entered into cell B1 to produce an output.
• Type the plus symbol (+). This is the symbol for addition in Excel.
• Type B2. This adds B2 to the formula as a cell reference
• Type the plug symbol (+).
• Type B3
• Press the ENTER key

The result of that underlying calculation will display in the cell. From the example, you see the display (or answer) is 15, but the cell itself actually contains the formula =B1+B2+B3

An alternative method to build a formula

Instead of typing B1, B2, and B3, you can type an (=) sign first and simply select the cell with your mouse and type (+) operator, select the next cell with your mouse and type (+) operator, and finally select the last cell in your formula. Don’t forget to press ENTER when you are done building your formula.

Use Cell References

Cell references enable Excel to dynamically produce new outputs when one or more inputs in the referenced cells are changed. Cell references also allow you to trace how outputs are being calculated in a formula. As a result, you should never use a calculator to determine a mathematical output and type the answer into the cell location of a worksheet. Doing so eliminates Excel’s cell-referencing benefits as well as your ability to trace a formula to determine how outputs are being produced.

A critical practice is to always use cell references in a formula if the value exists somewhere else on the spreadsheet. Do not type the values that exist in cell locations into an Excel formula. Again, this will eliminate Excel’s cell-referencing benefits, which means if the value in the cell location you are using in a formula is changed, Excel will not be able to produce a new output.

Excel Mathematical Operators

Does the Output of Your Formula Make Sense?

It is important to note that the accuracy of the output produced by a formula depends on how it is constructed. Therefore, always check the result of your formula to see whether it makes sense with data in your worksheet.

Summary:

Perhaps the most valuable feature of Excel is its ability to produce mathematical outputs using the data in a workbook with the construction of formulas for basic and complex mathematical computations.

• Formulas are intended to complete mathematical computations.
• All formulas start with an equal sign =
• Formulas created with cell references allows automatic recalculation of the mathematical outputs when one or more inputs are changed.
• When using numerical values in formulas and functions, only use universal constants that do not change, such as days in a week, months in a year, and so on.

Sources:

Business Information Systems: Design an App for That. Provided by: Saylor Academy. Located at: https://saylordotorg.github.io/text_business-information-systems-design-an-app-for-that/index.html. License: CC BY-NC-SA: Attribution-NonCommercial-ShareAlike

2.1 Formulas by Emese Felvegi, Noreen Brown, Barbara Lave, Julie Romey, Mary Schatz, Diane Shingledecker, and Robert McCarn is licensed under a Creative Commons Attribution 4.0 International License, except where otherwise noted.