# Unexpected (?) behaviour of OFFSET() in array formulas

1. ## Unexpected (?) behaviour of OFFSET() in array formulas

Hi all,

Ten numbers in A1:A10. Want to experiment with OFFSET in loop formulas,
so trying the following:
=SUM(OFFSET(A1, ROW(1:10)-1,0)) (array-entered of course).

As I understand it, the formula will loop over 1:10 and calculate, in
each turn, a reference to Ai. Thus, according to the spirit that has
worked in numerous instances of either SUMPRODUCT() or {SUM()}, namely
that a computed array is passed as argument, {SUM()} in this case has
10 cells to sum. Yet, the formula only recognizes the first cell A1.

Then I try to enter in cells B1:B10 as an array formula
=OFFSET(\$A\$1,ROW(1:10)-1,0)
hoping that I will get the mirror of A1:A10. I get #VALUE! in each
cell. Why am I not getting #VALUE! in the first formula? Seems
inconsistent to me. At least, if I got a #VALUE! in the first case, I
could attribute it the the computed array being an array of #VALUE!
(but it does not behave this way).

Furthermore, I am trying the more complex variant,
=SUM(IF(OFFSET(A1,ROW(1:10)-1,0)>3, OFFSET(A1,ROW(1:10)-1,0),0))
and I am getting #VALUE!

According to the documentation, OFFSET() will return #VALUE! if the
first argument is NOT a contiguous range. This is not happening. SUM()
will of course produce #VALUE! if one of the cells in the range already
has #VALUE!, but here we have the inconsistent behavior.

TIA
Kostis Vezerides

2. ## RE: Unexpected (?) behaviour of OFFSET() in array formulas

As I believe Peo recently explained, in your situation, the OFFSET function
needs a second evaluation to make it behave properly. Try this:

=SUM(N(OFFSET(A1, ROW(1:10)-1,0)))
Commit that array formula by holding down [Ctrl]+[Shift] when you press
[Enter]

Does that help?

***********
Regards,
Ron

"vezerid" wrote:

> Hi all,
>
> Ten numbers in A1:A10. Want to experiment with OFFSET in loop formulas,
> so trying the following:
> =SUM(OFFSET(A1, ROW(1:10)-1,0)) (array-entered of course).
>
> As I understand it, the formula will loop over 1:10 and calculate, in
> each turn, a reference to Ai. Thus, according to the spirit that has
> worked in numerous instances of either SUMPRODUCT() or {SUM()}, namely
> that a computed array is passed as argument, {SUM()} in this case has
> 10 cells to sum. Yet, the formula only recognizes the first cell A1.
>
> Then I try to enter in cells B1:B10 as an array formula
> =OFFSET(\$A\$1,ROW(1:10)-1,0)
> hoping that I will get the mirror of A1:A10. I get #VALUE! in each
> cell. Why am I not getting #VALUE! in the first formula? Seems
> inconsistent to me. At least, if I got a #VALUE! in the first case, I
> could attribute it the the computed array being an array of #VALUE!
> (but it does not behave this way).
>
> Furthermore, I am trying the more complex variant,
> =SUM(IF(OFFSET(A1,ROW(1:10)-1,0)>3, OFFSET(A1,ROW(1:10)-1,0),0))
> and I am getting #VALUE!
>
> According to the documentation, OFFSET() will return #VALUE! if the
> first argument is NOT a contiguous range. This is not happening. SUM()
> will of course produce #VALUE! if one of the cells in the range already
> has #VALUE!, but here we have the inconsistent behavior.
>
>
> TIA
> Kostis Vezerides
>
>

3. ## Re: Unexpected (?) behaviour of OFFSET() in array formulas

Hi!

Although I can't explain the exact technical reason of why it won't work
like that:

=SUM(OFFSET(A1, ROW(1:10)-1,0))

Try including the [height] argument and it will then work:

=SUM(OFFSET(A1, ROW(1:10)-1,,10))

For the "mirror" situation, again, I can't explain the technical reason, but
try:

=N(OFFSET(A\$1,ROW(1:10)-1,,))

But why would you use that versus:

=OFFSET(A\$1,,,10)

Biff

"vezerid" <vezerid@act.edu> wrote in message
> Hi all,
>
> Ten numbers in A1:A10. Want to experiment with OFFSET in loop formulas,
> so trying the following:
> =SUM(OFFSET(A1, ROW(1:10)-1,0)) (array-entered of course).
>
> As I understand it, the formula will loop over 1:10 and calculate, in
> each turn, a reference to Ai. Thus, according to the spirit that has
> worked in numerous instances of either SUMPRODUCT() or {SUM()}, namely
> that a computed array is passed as argument, {SUM()} in this case has
> 10 cells to sum. Yet, the formula only recognizes the first cell A1.
>
> Then I try to enter in cells B1:B10 as an array formula
> =OFFSET(\$A\$1,ROW(1:10)-1,0)
> hoping that I will get the mirror of A1:A10. I get #VALUE! in each
> cell. Why am I not getting #VALUE! in the first formula? Seems
> inconsistent to me. At least, if I got a #VALUE! in the first case, I
> could attribute it the the computed array being an array of #VALUE!
> (but it does not behave this way).
>
> Furthermore, I am trying the more complex variant,
> =SUM(IF(OFFSET(A1,ROW(1:10)-1,0)>3, OFFSET(A1,ROW(1:10)-1,0),0))
> and I am getting #VALUE!
>
> According to the documentation, OFFSET() will return #VALUE! if the
> first argument is NOT a contiguous range. This is not happening. SUM()
> will of course produce #VALUE! if one of the cells in the range already
> has #VALUE!, but here we have the inconsistent behavior.
>
>
> TIA
> Kostis Vezerides
>

4. ## Re: Unexpected (?) behaviour of OFFSET() in array formulas

Ron, Biff,
thank you both for your answers. I now have a workaround but I still
cannot understand the behavior. I wonder, is it a glitch or is it
supposed to be the expected behavior?

BTW, I searched in .misc with keywords: OFFSET evaluation Peo Sjoblom
but it returned an irrelevant thread only. Does one of you have the

Regards,
Kostis Vezerides

5. ## Re: Unexpected (?) behaviour of OFFSET() in array formulas

http://tinyurl.com/73erq

--

HTH

RP
(remove nothere from the email address if mailing direct)

"vezerid" <vezerid@act.edu> wrote in message
> Ron, Biff,
> thank you both for your answers. I now have a workaround but I still
> cannot understand the behavior. I wonder, is it a glitch or is it
> supposed to be the expected behavior?
>
> BTW, I searched in .misc with keywords: OFFSET evaluation Peo Sjoblom
> but it returned an irrelevant thread only. Does one of you have the
>
> Regards,
> Kostis Vezerides
>

6. ## Re: Unexpected (?) behaviour of OFFSET() in array formulas

Biff, pretty much summed it up just fine.
But the reference I recalled can be found if you search the Excel forum for:
+peo +"match and some other functions"

***********
Regards,
Ron

"vezerid" wrote:

> Ron, Biff,
> thank you both for your answers. I now have a workaround but I still
> cannot understand the behavior. I wonder, is it a glitch or is it
> supposed to be the expected behavior?
>
> BTW, I searched in .misc with keywords: OFFSET evaluation Peo Sjoblom
> but it returned an irrelevant thread only. Does one of you have the
>
> Regards,
> Kostis Vezerides
>
>

7. ## Re: Unexpected (?) behaviour of OFFSET() in array formulas

There was a dsicussion using indirect some years ago which pertains to the
same behaviour as offset where to be able to evaluate the array you need to
do it twice, here's a link to that discussion

http://tinyurl.com/7umpp

note that apart from N a double SUM like in

=SUM(SUM(OFFSET(A1, ROW(1:10)-1,0)))

or

=SUMPRODUCT(SUM(OFFSET(A1, ROW(1:10)-1,0)))

(entered normally)

and also TRANSPOSE

=SUM(TRANSPOSE(OFFSET(A1, ROW(1:10)-1,0)))

will work, I have put the question aside into the strange Excel behaviour
vault

--

Regards,

Peo Sjoblom

"vezerid" <vezerid@act.edu> wrote in message
> Ron, Biff,
> thank you both for your answers. I now have a workaround but I still
> cannot understand the behavior. I wonder, is it a glitch or is it
> supposed to be the expected behavior?
>
> BTW, I searched in .misc with keywords: OFFSET evaluation Peo Sjoblom
> but it returned an irrelevant thread only. Does one of you have the
>
> Regards,
> Kostis Vezerides
>

8. ## Re: Unexpected (?) behaviour of OFFSET() in array formulas

Thanks everybody for the replies. Several issues were clarified

I have a question to all of you: Are you using some special software
for archiving the messages? All of you often come up with links to very
specific threads. How do you do it? Do you have your own database, in
which you record the URL's with some keywords, is there a front end
which allows management of information such as this? Or is it simply
that you use smarter searches?

Thanks anyway,
Kostis Vezerides

9. ## Re: Unexpected (?) behaviour of OFFSET() in array formulas

Smarter searches? <bg> I use advanced google searches plus my memory, Ron De

--

Regards,

Peo Sjoblom

"vezerid" <vezerid@act.edu> wrote in message
> Thanks everybody for the replies. Several issues were clarified
>
> I have a question to all of you: Are you using some special software
> for archiving the messages? All of you often come up with links to very
> specific threads. How do you do it? Do you have your own database, in
> which you record the URL's with some keywords, is there a front end
> which allows management of information such as this? Or is it simply
> that you use smarter searches?
>
> Thanks anyway,
> Kostis Vezerides
>

10. ## Re: Unexpected (?) behaviour of OFFSET() in array formulas

Same as Peo, but I also save some of the KB entries and better replies in MS
Code Librarian.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"vezerid" <vezerid@act.edu> wrote in message
> Thanks everybody for the replies. Several issues were clarified
>
> I have a question to all of you: Are you using some special software
> for archiving the messages? All of you often come up with links to very
> specific threads. How do you do it? Do you have your own database, in
> which you record the URL's with some keywords, is there a front end
> which allows management of information such as this? Or is it simply
> that you use smarter searches?
>
> Thanks anyway,
> Kostis Vezerides
>

11. ## Re: Unexpected (?) behaviour of OFFSET() in array formulas

Oh, and of course, if you have been around here a while, a few things will
stick in your memory, which will help make a smarter search. Sometimes I
remember a keyword, or even better, a poster.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"vezerid" <vezerid@act.edu> wrote in message
> Thanks everybody for the replies. Several issues were clarified
>
> I have a question to all of you: Are you using some special software
> for archiving the messages? All of you often come up with links to very
> specific threads. How do you do it? Do you have your own database, in
> which you record the URL's with some keywords, is there a front end
> which allows management of information such as this? Or is it simply
> that you use smarter searches?
>
> Thanks anyway,
> Kostis Vezerides
>

12. ## Re: Unexpected (?) behaviour of OFFSET() in array formulas

Thanks for the tip, Peo.

Regards
Kostis Vezerides

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1