XL: OFFSET Accepts Negative Values for Height and Width

ID: Q184109

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows, versions 5.0, 5.0a, 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

When you use the OFFSET worksheet function and type a negative number for the Width argument or Height argument, you receive a result and not an error value. This behavior is incorrect.

If the Width argument or the Height argument is less than -1 (-2,-3,-4, and so on), the result of the OFFSET function is the error value #VALUE! unless you enter the formula as an array formula. When you enter the formula as an array, you receive a result and not an error value. This behavior is also incorrect.

NOTE: To enter a formula as an array formula in Microsoft Excel for Windows, enter the formula and press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

WORKAROUND

To work around this problem, use a positive integer number for the Width argument and the Height argument of the OFFSET worksheet function.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

According to Microsoft Excel Help, the OFFSET worksheet function requires that the Width argument and the Height argument be a positive number.

REFERENCES

For more information about the OFFSET worksheet function, click Contents And Index on the Help menu, click the Index tab in Microsoft Excel Help, type the following text

OFFSET worksheet function

and then double-click the selected text to go to the "OFFSET" topic. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: XL97 xl7 xl5

Keywords : kbdta xlformula
Version :
Platform : MACINTOSH WINDOWS
Issue type : kbbug