Excel: AppNote Complete Text "Entering Formulas" (ME0188)

Last reviewed: October 7, 1997
Article ID: Q50897

1.x 2.20 3.00 MACINTOSH appnote ME0188 kbother

The information in this article applies to:

Microsoft Excel for the Macintosh, versions 1.x, 2.2, and 3.0

SUMMARY

The following information comes from an application note that outlines the procedures for entering formulas in Microsoft Excel for the Macintosh. To obtain this application note, call Microsoft Product Support Services at (425) 635-7080.

  Microsoft(R) Product Support Services Application Note (Text File)
                       ME0188: ENTERING FORMULAS

                                                   Revision Date: 7/91
                                                      No Disk Included

The following information applies to Microsoft Excel for the Macintosh(R), versions 1.0, 1.03, 1.04, 1.06, 1.5, 2.2, and 3.0.

 --------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY    |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an            |
| Application Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY      |
| KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO    |
| THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A     |
| PARTICULAR PURPOSE. The user assumes the entire risk as to the     |
| accuracy and the use of this Application Note. This Application    |
| Note may be copied and distributed subject to the following        |
| conditions: 1) All text must be copied without modification and    |
| all pages must be included; 2) If software is included, all files  |
| on the disk(s) must be copied without modification [the MS-DOS(R)  |
| utility DISKCOPY is appropriate for this purpose]; 3) All          |
| components of this Application Note must be distributed together;  |
| and 4) This Application Note may not be distributed for profit.    |
|                                                                    |
| Copyright 1991-1993 Microsoft Corporation. All Rights Reserved.    |
| Microsoft and MS-DOS are registered trademarks and Windows         |
| is a trademark of Microsoft Corporation.                           |
| Macintosh is a registered trademark of Apple Computer, Inc.        |
 --------------------------------------------------------------------

GENERAL INFORMATION

When you are typing formulas in the formula bar, it is a good idea to type all function names and column references in lowercase letters. When you finally enter the formula, Excel will convert the functions it recognizes to uppercase letters. You can then double-check the syntax of names not converted to uppercase and make any changes necessary. Excel will not convert items enclosed in quotation marks, or names defined on the worksheet to uppercase letters, unless the name was originally typed in uppercase letters.

If you are in the process of editing a formula and need to leave the formula bar for some reason, you can do so without losing what you've entered. Just delete the equal sign at the beginning of the formula and press RETURN. This method will enter the formula as text, without checking its syntax, and allow you to do other work on the worksheet. You can then finish editing the formula by clicking the cell and reinserting the equal sign to the beginning of the formula.

Use Names for Linked References

When linking between different spreadsheets in Microsoft Excel version 1.0 or 2.0, use named references instead of cell references. This method allows you to insert, delete, and move cells on the dependent worksheet without affecting the link. In Microsoft Excel version 3.0, the use of defined names is not necessary because linked cell references behave in the same manner as references to cells on the same worksheet.

Copying Formulas

It is possible to copy a formula with relative references to separate cells in a worksheet and still have it maintain the references to the same cells. To do this, highlight the formula in the formula bar, choose Copy from the Edit menu, and click the "X" to the left of the formula bar. Then, select the cell you want to paste the formula into, click the formula bar, and choose Paste from the Edit menu. The formula will be copied into the formula bar of the active cell exactly as the formula appeared in the cell from which it was copied.

Calculate Now

When editing a complex formula, it is sometimes helpful to know the value that will be returned by a portion of the formula. To do this, select the particular references or functions in the formula bar you want to know the value of, hold down the COMMAND key, and press the EQUAL SIGN key (=). The selected area in the formula bar will be replaced with its computed value as long as the formula begins with an equal sign.

   NOTE: In Microsoft Excel versions 1.0 and 2.0, there is no way to
   "undo" a formula that has been entered with the calculated values
   showing; therefore, the selected area must be re-entered. To avoid
   having to do this, after calculating the value, click the "X" to
   the left of the formula in the formula bar to return the cell to
   its previous state.

Array Formulas

Array formulas are useful both for entering values in a range of cells using one formula and for creating more complex formulas.

To enter an array in a range of cells, select the cells in which you will be entering the formula. Then, type the array formula and press COMMAND+ENTER. This will place braces ({}) around the formula in the formula bar and enter the corresponding array in the selected cells. Once a range of cells has been defined as an array, any operation must be performed on the entire array.

To edit or clear the values of the array, select the entire area and make the necessary changes to the formula in the formula bar. In versions of Excel earlier than 2.2, you can select the entire range by double-clicking any cell in the array. In Excel versions 2.2 and later, you can select the entire range by pressing COMMAND+SHIFT and double-clicking any cell in the array. If you do not have the entire range selected, you will get either the error message "Illegal Array Operation" or "Can't Change Part of an Array" when you try to enter a new formula in one of the cells of the range. If this happens, Excel will not let you leave the formula bar until you click the "X" to the left of the formula bar, which will restore the original array formula. To edit these cells, select the entire array and re-enter the necessary changes by pressing COMMAND+ENTER.

Example 1

To find the values for column C of the following table by using an array formula, select C2 through C6 and type the formula "=A2:A6*B2:B6" (without the quotation marks). After typing the formula, press COMMAND+ENTER. Column C will be filled with the appropriate values.

   NOTE: This method takes up less memory than entering individual
   formulas into each cell.

          A     B     C
     -------------------
     1    X     Y    X*Y
     2    1     2     2
     3    2     4     8
     4    3     6    18
     5    4     8    32
     6    5    10    50

Example 2

In the following table, to have "Overdrawn" entered in column C for all accounts with a negative balance, select C2 through C7 and type the formula:

=IF(B2:B7<0,"Overdrawn","")

Press COMMAND+ENTER to enter this as an array formula.

           A                B      C
   --------------------------------------
   1   Account #      Balance   Status
   2     12-534     $1,200.00
   3     12-535      ($45.00)  Overdrawn
   4     12-536     $2,500.00
   5     12-537       $750.00
   6     12-538     ($350.00)  Overdrawn
   7     12-539      ($75.00)  Overdrawn

In the table below, to enter in column D a 5-percent service charge on the amount overdrawn, select D2 through D7 and type the formula

   =IF(B2:B7<0,-B2:B7*5%,"")

and press COMMAND+ENTER.

To find the total owed in column E, select E2 through E7 and type the formula

   =IF(B2:B7<0,-B2:B7+D2:D7,"")

and press COMMAND+ENTER.

To find the number of overdrawn accounts, select B9 and type the formula

   =SUM(IF(C2:C7="Overdrawn",1,0))

and press COMMAND+ENTER.

Likewise, to find the total amount overdrawn in column B, select B10 and type the formula

   =SUM(IF(B2:B7<0,-B2:B7,0))

and press COMMAND+ENTER. The resulting table is shown below.

       A                          B     C         D          E
   ----------------------------------------------------------------
   1   Account #            Balance Status      Finance Total Owed
                                                 Charge
   2   12-534             $1,200.00
   3   12-535              ($45.00) Overdrawn     $2.25     $47.25
   4   12-536             $2,500.00
   5   12-537               $750.00
   6   12-538             ($350.00) Overdrawn    $17.50    $367.50
   7   12-539              ($75.00) Overdrawn     $3.75     $78.75
   8
   9   # Overdrawn:               3
   10  Total Overdrawn:     $470.00
   11  Total Owed           $493.50

   NOTE: To find the total owed, select B11 and enter "=SUM(E2:E7)"
   (without the quotation marks).

KBCategory: kbother
KBSubcategory:
Additional reference words: 1.0 1.00 1.03 1.04 1.06 1.5 1.50 2.2
2.20 3.0 3.00

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 7, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.