OFFSET "Bug": Negative Height argument

In HELP for the OFFSET function, it is stated that Height (and Width) must be positive numbers.  Since the function seems to work with negative numbers for these arguments, merely reversing the direction of the range, one might think that this is a documentation error.

However, searching the MSKB reveals that this has been flagged as abnormal behavior, that Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

It seems this behavior has existed at least since Microsoft Excel 5.0 !!! , so they are taking their time about fixing it :-)

I don't know if it is "a problem" in Excel 2010 but perhaps someone could let TPTB know that this is a useful feature.
Since the row and column offsets could be negative it was not strictly necessary to allow negative height and width. e.g. =OFFSET(Z9,1,2,-3,-4) is the same as =OFFSET(Z9, -1, -1, 3, 4)
 
However it is unlikely that they will change the behaviour now as it would break existing workbooks and that is something they strive not to do. 
Microsoft Excel MVP, Oxford, England. www.manville.org.uk

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Thanks for your thoughts, Bill.

I hope you are correct.  But given the kinds of things that MS has discontinued in the past, and the fact that this behavior is explicitly stated as being incorrect, I am not as sanguine as you.  Especially if this "feature" gets broken in some future update, I doubt that fixing it will have any kind of priority.
Ron

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated September 16, 2020 Views 695 Applies to: