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