Excel: Functions Recalculated If New Data or Cells Entered

ID: Q81856

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for the Macintosh, versions 2.20, 3.x, 4.x, 5.0

SUMMARY

In Microsoft Excel, when you use the AREAS(), INDEX(), OFFSET(), CELL(), INDIRECT(), ROWS(), COLUMNS(), NOW(), RAND(), or TODAY() functions, the formulas that use these functions are recalculated when either of the following occur:

You enter new data

You insert or delete cells, rows, or columns on the worksheet

This recalculation occurs regardless of whether cells are dependent on the new data.

WORKAROUNDS

To disable automatic recalculation, do either of the following:

Choose Options from the Tools menu, select the Calculation tab and select the Manual option under Calculation (version 5.0) or choose Calculation from the Options menu and select the Manual option under Calculation.

Avoid using the following functions:
AREAS()
INDEX()
OFFSET()
CELL()
INDIRECT()
ROWS()
COLUMNS()
NOW()
RAND()

MORE INFORMATION

This functionality can be compared to the VOLATILE() macro function and the Visual Basic, Applications Editions Volatile method (Version 5.0 only) in Microsoft Excel. You can use the VOLATILE function, or the Volatile method in a user-defined function to cause the custom function to be recalculated under the same circumstances of data entry, insertions, and deletions.

Example for Microsoft Excel version 5.0

In a new module, enter the following:


      Function FeetPerSecond(MilesPerHour as Double) as Double
         Application.Volatile
         FeetPerSecond=MilesPerHour*5280/3600
      End Function

On a worksheet, select the range A1:C50.

In the Formula bar, type the formula =feetpersecond(60).

NOTE: This value must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

Enter any number into cell D1 and note that the worksheet is recalculated.

Because this user-defined function contains the Volatile method, this function is recalculated every time you enter data or insert or delete cells, columns or rows into any open worksheet. If the FeetPerSecond user- defined function did not contain the Volatile method, entering data into a cell would not cause a recalculation to occur.

Example for Microsoft Excel versions 3.0 and 4.0

Enter the following into a macro sheet:


   A1:   FeetPerSecond
   A2:   =ARGUMENT("MilesPerHour",1)
   A3:   =VOLATILE()
   A4:   =RETURN(MilesPerHour*5280/3600)

Select cell A1 and choose Define Name from the Formula menu. Select the Function option in the Macro section and choose OK.

Open a new worksheet and select cells A1:C50.

Choose Paste Function from the Formula menu. Scroll to the end of the listed functions. Select MACRO1!FeetPerSecond(MilesPerHour) and choose the OK button.

In the formula bar, replace MilesPerHour with 60.

NOTE: This value must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows or Microsoft Excel for OS/2, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

Microsoft Excel will return the value 88 in cells A1:C50.

Enter any number into cell D1 and note that the worksheet is recalculated.

Because this user-defined function has been defined with the VOLATILE function, this function will be recalculated every time you enter data or insert or delete cells, columns or rows into any open worksheet. If the FeetPerSecond user-defined function did not contain the VOLATILE function, entering data into a cell would not cause a recalculation to occur.

The functions listed above can be thought of as containing this VOLATILE function or method.

NOTE: In versions of Microsoft Excel earlier than version 3.0, the function OFFSET() was available as a macro function only, not as a worksheet function. The macro function VOLATILE() is not available in versions of Microsoft Excel earlier than version 3.0.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, page 451
"Microsoft Excel Function Reference," version 3.0, page 247

Additional query words: 7.00 2.00 2.01 2.10 2.20 2.21 3.00 4.00 4.00a 5.00 workbook udf

Keywords :
Version : MACINTOSH:2.20,3.x,4.x,5.0; WINDOWS:2.x,3.x,4.x,5.0,5.0c,7.0; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type :