Microsoft KB Archive/99365

= Microsoft Knowledge Base =

Excel: Cannot Use Percent Sign in Function with Array Constant
Last reviewed: March 27, 1997

Article ID: Q99365

2.X 3.00 4.00 4.00a 5.00 7.00 | 2.2 3.00 | 2.X 3.00 4.00 5.00

WINDOWS                      | OS/2     | MACINTOSH kbusage

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for OS/2, versions 2.2, 3.0
 * Microsoft Excel for the Macintosh, versions 2.2 ,3.0 ,4.0

SUMMARY
In Microsoft Excel, you can use the percent sign (%) in most formulas to express a number in percentage format. For example, Microsoft Excel will understand the following formula

=SUM(1%+2%) and will return a result of .03 (3%). However, you cannot use the percent sign (%) in formulas that contain array constants.

Workaround
To use numbers in percentage format in an array constant, do either of the following

 Reference a range of cells that contain numbers with percent signs. -or- Use the actual values in the array constant.

MORE INFORMATION
In Microsoft Excel version 4.0, the FVSCHEDULE function, which returns the future value of an initial principal after applying a series of compound interest payments, will not accept any number with a percent symbol. This occurs because the FVSCHEDULE function uses an array constant for the second argument.

Therefore, a formula such as the following

=FVSCHEDULE(1,{9%,11%,10%}) will result in the error message &quot;Error in Formula.&quot;

To use the FVSCHEDULE function with numbers expressed in percentages, enter the arguments in either of the following formats

=FVSCHEDULE(1,{0.09,0.11,0.1})

-or-

=FVSCHEDULE(1,A1:A3) where the range from A1 to A3 contains numbers with percentages. These two examples will return the future value of one dollar with different interest rates. The cells being referenced can contain numbers with percent signs or actual values.