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.

Will someone enlighten please?

TIA
Kostis Vezerides

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.
>
> Will someone enlighten please?
>
> TIA
> Kostis Vezerides
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> Will someone enlighten please?
>
> TIA
> Kostis Vezerides
>

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
link to the thread?

Regards,
Kostis Vezerides

http://tinyurl.com/73erq

--

HTH

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


"vezerid" <[email protected]> wrote in message
news:[email protected]...
> 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
> link to the thread?
>
> Regards,
> Kostis Vezerides
>

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
> link to the thread?
>
> Regards,
> Kostis Vezerides
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> link to the thread?
>
> Regards,
> Kostis Vezerides
>

Thanks everybody for the replies. Several issues were clarified
reading the older threads.

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

Smarter searches? <bg> I use advanced google searches plus my memory, Ron De
Bruin has an excel add-in that will do google searches

http://www.rondebruin.nl/Google.htm




--

Regards,

Peo Sjoblom


"vezerid" <[email protected]> wrote in message
news:[email protected]...
> Thanks everybody for the replies. Several issues were clarified
> reading the older threads.
>
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> Thanks everybody for the replies. Several issues were clarified
> reading the older threads.
>
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
> Thanks everybody for the replies. Several issues were clarified
> reading the older threads.
>
> 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
>

Thanks for the tip, Peo.

Regards
Kostis Vezerides