Microsoft Office Tutorials and References

In Depth Information

**Introduction**

Introduction

Once you enter data in a worksheet, you’ll want to add formulas to perform calculations.

Microsoft Excel can help you get the results you need. Formulas can be very basic entries

to more complex ones. The difficulty of the formula depends on the complexity of the res-

ult you want from your data. For instance, if you are simply looking to total this months

sales, then the formula would add your sales number and provide the result. However, if

you were looking to show this months sales, greater than $100.00 with repeat customers,

you would take a bit more time to design the formula.

Because Excel automatically recalculates formulas, your worksheets remain accurate

and up-to-date no matter how often you change the data. Using absolute cell references

anchors formulas to a specific cell. Excel provides numerous built-in functions to add to

your worksheet calculations. Functions, such as AVERAGE or SUM, allow you to per-

form a quick formula calculation.

Another way to make your formulas easier to understand is by using name ranges in

them. Name ranges—a group of selected cells named as a range—can help you understand

your more complicated formulas. It is a lot easier to read a formula that uses name ranges,

then to look at the formula and try to decipher it. Excel offers a tool to audit your work-

sheet. Looking at the “flow” of your formula greatly reduces errors in the calculation. You

can see how your formula is built, one level at a time through a series of arrows that point

out where the formula is pulling data from. As you develop your formula, you can make

corrections to it.

Understanding Formulas

Introduction

A formula calculates values to return a result. On an Excel worksheet, you can create a

formula using constant values (such as 147 or $10.00), operators (shown in the table), ref-

erences, and functions. An Excel formula always begins with the equal sign (=).

A
constant
is a number or text value that is not calculated, such as the number 147, the

text “Total Profits”, and the date 7/22/2013. On the other hand, an
expression
is a value

that is not a constant. Constants remain the same until you or the system change them. An

operator
performs a calculation, such as + (plus sign) or - (minus sign). A cell
reference

is a cell address that returns the value in a cell. For example, A1 (column A and row 1)

returns the value in cell A1 (see table below).