Menu

Excel 2003 Spreadsheet Formulas

If you need to create an Excel Spreadsheet Formula, this Microsoft Excel tutorial will teach you how. We take you step-by-step through the process of creating Microsoft Excel spreadsheet formulas and performing mathematical calculations in Microsoft Excel.

Lesson 3: Numbers and Mathematical Calculations

Microsoft Excel has many functions that you can use. Functions allow you to quickly and easily find an average, the highest number, the lowest number, a count of the number of items in a list, and make many other useful calculations.

Reference Operators

Reference operators refer to a cell or a group of cells. There are two types of reference operators, range  and union.

A range reference refers to all the cells between and including the reference. A range reference consists of two cell addresses separated by a colon. The reference A1:A3 includes cells A1, A2, and A3. The reference A1:C3 includes A1, A2, A3, B1, B2, B3, C1, C2, and C3.

A union reference includes two or more references. A union reference consists of two or more cell addresses separated by a comma. The reference A7,B8,C9 refers to cells A7, B8, and C9.

Functions

Microsoft Excel has a set of prewritten formulas called functions. Functions differ from regular formulas in that you supply the value but not the operators, such as +, -, *, or /. For example, you can use the SUM function to add. When using a function, remember the following:

        Use an equal sign to begin a formula.

        Specify the function name.

        Enclose arguments within parentheses.

        Use a comma to separate arguments.

Here is an example of a function:

=SUM(2,13,A1,B27)

In this function:

The equal sign begins the function.

SUM is the name of the function.

2, 13, A1, and B27 are the arguments.

Parentheses enclose the arguments.

A comma separates the arguments.

The SUM function adds the arguments together. In the exercises that follow, we will look at various functions.

Typing a Function

  1. Open Microsoft Excel.
  2. Type 12 in cell B1.
  3. Press Enter.
  4. Type 27 in cell B2.
  5. Press Enter.
  6. Type 24 in cell B3.
  7. Press Enter.
  8. Type =SUM(B1:B3) in cell A4.
  9. Press Enter. Microsoft Excel sums cells B1 to B3.

Alternate Method: Entering a Function by Using the Menu

  1. Type 150 in cell C1.
  2. Press Enter.
  3. Type 85 in cell C2.
  4. Press Enter.
  5. Type 65 in cell C3.
  6. Press Enter. Your cursor should be in cell C4.
  7. Choose Insert > Function  from the menu.
  8. Choose Math & Trig in the Or Select A Category box.
  9. Click Sum in the Select A Function box.
  10. Click OK. The Functions Arguments dialog box opens.
  11. Type C1:C3 in the Number1 field, if it does not automatically appear.
  12. Click OK. Microsoft Excel sums cells C1 to C3.
  13. Move to cell A4.
  14. Type the word Sum.
  15. Press Enter.

As you learned in Lesson 2, you can also calculate a sum by using the Sum icon.

Calculating an Average

You can use the AVERAGE function to calculate the average of a series of numbers.

  1. Move your cursor to cell A6.
  2. Type Average. Press the right arrow key to move to cell B6.
  3. Type =AVERAGE(B1:B3).
  4. Press Enter. The average of cells B1 to B3, which is 21, will appear.

Calculating an Average by Using the Sum Icon

In Microsoft Excel XP, you can use the Sum icon to calculate an average.

  1. Move your cursor to cell C6.
  2. Click the drop-down arrow next to the Sum icon.
  3. Click Average.
  4. Highlight C1 to C3.
  5. Press Enter. The average of cells C1 to C3, which is 100, appears.

Calculating Min

You can use the MIN function to find the lowest number in a series of numbers.

  1. Move your cursor to cell A7.
  2. Type Min.
  3. Press the right arrow key to move to cell B7.
  4. Type = MIN(B1:B3).
  5. Press Enter. The lowest number in the series, which is 12 appears.

Calculating Max

You can use the MAX function to find the highest number in a series of numbers.

  1. Move your cursor to cell A8.
  2. Type Max.
  3. Press the right arrow key to move to cell B8.
  4. Type = MAX(B1:B3).
  5. Press Enter. The highest number in the series, which is 27, appears.

Note: You can also use the drop-down menu next to the Sum icon to calculate minimums and maximums.

Calculating Count

You can use the count function to count the number of items in a series.

  1. Move your cursor to cell A9.
  2. Type Count
  3. Press the right arrow key to move to cell B9.
  4. Click the down arrow next to the Sum icon.
  5. Click Count.
  6. Highlight B1 to B3.
  7. Press Enter. The number of items in the series, which is 3 appears.

Filling Cells Automatically

You can use Microsoft Excel to fill cells automatically with a series. For example, you can have Excel automatically fill in times, the days of the week or months of the year, years, and other types of series. Days of the week and months of the year fill in a similar fashion. The following demonstrates filling the days of the week:

  1. Move to Sheet2.
  2. Move to cell A1.
  3. Type Sun.
  4. Move to cell B1.
  5. Type Sunday.
  6. Highlight cells A1 to B1.
  7. Bold cells A1 to B1.
  8. Find the small black square in the lower right corner of the highlighted area. This is called the Fill Handle.
  9. Grab the Fill Handle and drag with your mouse to fill cell A1 to B24. Note how the days of the week fill the cells in a series. Also, note that the Auto Fill Options icon appears.

Fill handle

  1. Click the Auto Fill Options icon.
  2. Choose the Copy Cells radio button. The entry in cells A1 and B1 are copied to all the cells highlighted.
  3. Click the Auto Fill Options icon again.
  4. Choose the Fill Series radio button. The cells fill as a series from Sunday to Saturday again.
  5. Click the Auto Fill Options icon again.
  6. Choose the Fill Without Formatting radio button. The cells fill as a series from Sunday to Saturday, but the entries are not bolded.
  7. Click the Auto Fill Options icon again.
  8. Choose the Fill Weekdays radio button. The cells fill as a series from Monday to Friday.

Some of the entries in column B are too long to fit in the column. You can quickly adjust the column width to fit the longest entry.

  1. Move your cursor over the line that separates column B and C. The Width Indicator appears.

Adjust width

  1. Double-click. The Column adjusts to fit the longest entry.

The following demonstrates filling time:

  1. Type 1:00 into cell C1.
  2. Grab the Fill Handle and drag with your mouse to highlight cells A1 to A24. Note that each cell fills using military time.
  3. Press Esc and then click anywhere on the worksheet to remove the highlighting.

To change the format of the time:

  1. Select cells C1 to C24.
  2. Choose Format > Cells  from the menu.
  3. Choose the Number tab.
  4. In the Category box, choose Time.
  5. In the Type box, choose 1:30 PM.
  6. Click OK. The time is no longer in military time.

You can also fill numbers.

Type a 1 in cell D1.

  1. Grab the Fill Handle and drag with your mouse to highlight cells D1 to D24. The number 1 fills each cell.
  2. Click the Auto Fill Options icon.
  3. Choose the Fill Series radio button. The cells fill as a series starting with 1, 2, 3.

Here is another interesting fill feature.

  1. Go to cell E1.
  2. Type Lesson 1.
  3. Grab the Fill Handle and drag with your mouse to highlight cells E1 to E24.
  4. The cells fill in as a series: Lesson 1, Lesson 2, Lesson 3, and so on.

Printing

The simplest way to print is to click the Print icon located on the Standard toolbar. Dotted lines will appear on your screen after you click the print icon. The dotted lines indicate the right, left, top, and bottom edges of your printed pages.

Print Preview

There are many print options. You can select print options options in Page Setup or in Print Preview. In Print Preview, you can see the results of your selections onscreen. You can use print options to:

  • Determine whether to print landscape or portrait. If you print portrait on an 8 1/2 by 11 sheet of paper, the length across the top of your page will be 8 1/2 inches. If you print landscape on an 8 1/2 by 11 sheet of paper, the length across the top of your page will be 11 inches.
  • Scale your document. If your data is small in comparison to the page, you may want to scale upward so the data fills the entire page. If your data is too large to fit on the page, you may want to scale downward.
  • Specify how many pages wide and how many pages long you want your printed document to be.
  • Select the paper size and print quality.
  • Set the first page number.

If you choose the Margins tab, you can:

  • Set the size of your margins including your header and footer margins.
  • Center your spreadsheet horizontally and/or vertically on the page.

If you choose the Header/Footer tab, you can select headers and footers. A header is text that appears at the top of every page. A footer is text that appears at the bottom of every page. You can use headers and footers to insert page numbers, dates, and other information.

To choose a header:

  1. Choose the Header/Footer tab.
  2. Click the down arrow next to the Header field to open the drop-down box for the header field.
  3. Choose a Header from the list.

To choose a footer:

  1. Choose the Header/Footer tab.
  2. Click the down arrow next to the Footer field to open the drop-down box for the Footer field.
  3. Choose a Footer from the list.

Click the Custom Header or Custom Footer button to customize your headers and footers.

Print

Use the Left Section to place your options on the left side of the page, the Center Section to place your options in the center of the page, and the Right Section to place your options 9on the right side of the page.

The Sheet tab has options that allow you to choose which rows and columns will repeat at the left and the top of the page. It also has options that allows you to determine whether gridlines and/or row column headings print

To preview and print your spreadsheet:

  1. Choose File > Preview  from the menu.
  2. Click Setup.
  3. Choose the Page tab.
  4. Choose Portrait.
  5. In the Adjust To field, type 110% to set the size to 110%,.
  6. Choose the Margin tab.
  7. Check the Horizontally box in the Center On Page frame to center your spreadsheet horizontally.
  8. Click OK.
  9. Click Print. The Print dialog box opens.
  10. Click OK to print the file.

Saving Your File

To save your file:

  1. Choose File>Save  from the menu.
  2. Go to the directory in which you want to save your file.
  3. Type lesson3 in the File Name field.
  4. Click Save.

Closing Microsoft Excel

This is the end of Lesson 3. Close Microsoft Excel.

  1. Choose File > Exit  from the menu.