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

## Bookmarks