Microsoft KB Archive/101168

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

XL: Finding the Percentage of Change Between Values



The information in this article applies to:


  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0, 5.0c
  • Microsoft Excel for OS/2, versions 2.2, 3.0
  • Microsoft Excel for the Macintosh, versions 2.2, 3.0, 4.0, 5.0, 5.0a
  • Microsoft Excel for Windows 95, versions 7.0, 7.0a
  • Microsoft Excel 97 for Windows
  • Microsoft Excel 98 Macintosh Edition





SUMMARY

In Microsoft Excel, there is no built-in function to calculate the percentage of change from one value to another. However, you can use the following formula to calculate the percentage change:


   =(new_value-original_value)/ABS(original_value) 



Original_value represents the value that the percentage of change is based on and new_value represents the value that has changed.

The following alternate formula may also be used:


   =(new_value/original_value)-1 



To display the resulting value as a percentage value, format the cell containing the formula with a percentage format.




MORE INFORMATION

Example

To see an example, follow these steps:


  1. In a new worksheet, enter the following values:

    A1: Sales for 1990     B1: Sales for 1991
    A2: $100,000           B2: $175,000
    A4: Percent Change     B4: =(B2-A2)/ABS(A2) 



    If you use the alternate formula, enter the formula "=(B2/A2)-1" (without the quotation marks) in cell B4.

  2. With cell B4 selected, choose Number from the Format menu and choose a percent format. (In Microsoft Excel 5.0 and later, click Cells on the Format menu, select the Number tab, and select a percent format.)

    You will see that the 1991 sales increased 75-percent over the sales for 1990. This is the percent of change.

Additional query words: 2.00 2.01 2.10 2.20 2.21 3.00 4.00 4.00a decrease difference 5.00 5.00a 5.00c 7.00 7.00a 97 98 XL98 XL97 XL7 XL5 XL4 XL3

Keywords : xlformula
Version : WINDOWS:2.0,3.0,4.0,5.0,7.0,97; MACINTOSH:2.2,3.0,4.0,5.0,98
Platform : MACINTOSH OS/2 WINDOWS
Issue type : kbinfo
Technology :


Last Reviewed: April 6, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.