XL2007: I need a sumproduct formula that will transpose as I copyacross columns

[CTB]

Hello all,

I need a sumproduct formula that will transpose as I copy across
columns. I have tried:

=SUMPRODUCT(OFFSET(Balances!$G$9,RowOffset,0,1,2),OFFSET(Rates!$G
$9,RowOffset,0,1,2))

(FYI: RowOffset is a named range: =COLUMN()-5 )

It doesn't seem like sumproduct likes to play with offset. It appears
the offsets are returning the right ranges, but trying to use offsets
with sumproduct no workie.

 

[joeu2004]

I need a sumproduct formula that will transpose as I
copy across columns.  I have tried:

=SUMPRODUCT(OFFSET(Balances!$G$9,RowOffset,0,1,2),
OFFSET(Rates!$G$9,RowOffset,0,1,2))

(FYI:  RowOffset is a named range:  =COLUMN()-5  )

It doesn't seem like sumproduct likes to play with offset.

Actually, the problem is with COLUMN(): it returns an array in that
context. I don't know why.

Try changing RowOffset to =MIN(COLUMN())-5

 

[joeu2004]

=SUMPRODUCT(OFFSET(Balances!$G$9,RowOffset,0,1,2),
OFFSET(Rates!$G$9,RowOffset,0,1,2))
(FYI:  RowOffset is a named range:  =COLUMN()-5  )

[....]
Try changing RowOffset to =MIN(COLUMN())-5

Although that works, a better solution is to define RowOffset as
=COLUMN()-4 [1], and change your formula to:

=SUMPRODUCT(INDEX(Balances!$G$9:$H$20,RowOffset,0),
INDEX(Rates!$G$9:$H$20,RowOffset,0))

Of course, you could use RowOffset+1 in the formula if you need to
keep the definition of RowOffset as is for other purposes.

Using INDEX in this form (zero column offset) is more efficient than
using OFFSET. OFFSET is a volatile funtion; INDEX is not. Go figure!

-----
Endnotes

[1] Even though it is not required when using INDEX, you might want to
use the kludge =MIN(COLUMN())-4 so that RowOffset is reliable in other
contexts.