Lesson 23: Writing Formulas in Microsoft Excel XP

/en/excelxp/planning-your-excel-xp-spreadsheet/content/

Writing formulas in Microsoft Excel is as simple or complicated as you make it. If you’re not very strong in math, then you might write simple formulas. If you’re very good with numbers, then you likely write more sophisticated formulas. Either way, Microsoft Excel is a powerful program that is suited to either of your needs.

In its simplest form, Excel can be used to create basic formulas—for example, to add two numbers together. The formula =2+3 adds the numbers 2 and 3. The cell in which the formula is defined shows the answer: 5.

But what if you work with a lot of numbers, or with numbers that change frequently? You would instead write a formula that includes cell references. Type a number in one cell (cell A1), type a second number in a different cell (cell A2), then write a formula that adds cells A1 and A2. It doesn’t matter what numbers appear in these cells or how frequently they change. The formula will adjust the answer depending on whichever numbers are fed into it.

Excel comes equipped with a number of predefined formulas, called functions, that simply the formula-writing process. Functions are common formulas, such as adding a range of numbers. Why write a formula if Excel has a function that does the same thing?

Say, for example, that you want to find the average of 12 numbers written in 12 different cells. You could write the following formula:

=(B1+B2+B3+B4+B5+B6+B7+B8+B9+B10+B11+B12)/12

But it would be better to use the Average function instead:

=AVERAGE(B1:B12)

Quick. Simple. And you get the same answer.

Excel has many different functions. The best way to become familiar with them is to display the Insert Function dialog box. You can type a description of what you want the function to do, or you could browse through a list of functions in any of 12 categories. Find the function you want, and select it.

If you work with numbers a lot and are pretty good at writing mathematical equations, then you will want to write more sophisticated formulas that meet your special needs. This is not a problem in Excel. If you can say what you want to do in a sentence, then you can write a formula to do it in Excel.