sumproduct with offset

Posted by greg on January 03, 2002 12:17 PM

I would like to take various-sized averages of selected rows of a matrix.

Say I have a matrix D2:BI453 whose columns represent days and rows represent IDs.
I want some variable number of contiguous days average of specific IDs.

When I wanted only 1 day, it was easy (though not elegant): I entered a 1 in column C on the row of each ID to include, then used SUMPRODUCT(C2:C453,BI2:BI453)

Then someone asked for 7 days. So:

(SUMPRODUCT(C2:C453,OFFSET(Blocks!BI2:BI453,0,0)) +
SUMPRODUCT(C2:C453,OFFSET(Blocks!BI2:BI453,0,-1)) +
SUMPRODUCT(C2:C453,OFFSET(Blocks!BI2:BI453,0,-2)) +
SUMPRODUCT(C2:C453,OFFSET(Blocks!BI2:BI453,0,-3)) +
SUMPRODUCT(C2:C453,OFFSET(Blocks!BI2:BI453,0,-4)) +
SUMPRODUCT(C2:C453,OFFSET(Blocks!BI2:BI453,0,-5)) +
SUMPRODUCT(C2:C453,OFFSET(Blocks!BI2:BI453,0,-6))) /7

Now the number of days to average changes frequently. I tried
SUMPRODUCT(C2:C453,OFFSET(Blocks!BI2:BI453,0,0, 453, 7)) but get #VALUE!

How can I do this?

Posted by Aladin Akyurek on January 03, 2002 12:47 PM

Greg --

I'm a bit unsettled by your question. Is it possible that you post some sample data--scaled down of course?

If you'd like to do that, here is a procedure:

Activate an empty cell, type =, select 30 rows of your (scaled down) data, go the Formula Bar, hit F9, copy what you see between the braces, and past it in the follow up, along with expected result(s) if possible.

Aladin

========

Posted by greg on January 04, 2002 10:29 AM

This turns out to be kind of an AverageIf with 2 conditions and several variables. I found a CSE way to do it. Here's my (simplified) formula:

=AVERAGE(IF(C2:C453=1,OFFSET(C2:C453,0,MATCH(A1,1:1)-3+B1,452,0)))

where cell A1 contains the date I'm looking for and B1 contains the number of days I want to average.

(The matrix of data is actually C2:BI453)

-------------