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

[vezerid]

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

 

[Guest]

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

 

[Biff]

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]

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

 

[Bob Phillips]

http://tinyurl.com/73erq

--

HTH

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

 

[Guest]

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

 

[Peo Sjoblom]

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

 

[vezerid]

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

 

[Peo Sjoblom]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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]

Thanks for the tip, Peo.

Regards
Kostis Vezerides