PDA

View Full Version : Solved: Computing Average Using Offset



rangudu_2008
01-21-2009, 08:32 AM
I've a workbook which has 2 sheets. The data from the 1st sheet is displayed in the 2nd using lookup function. The data in the first sheet contains a set of codes for which certain numbers are populated in a tabular format. There are 2 such tables, one for each segment C and another for segment W. These numbers that are stored against each code are classified into 3 types - XXXXXX, YYYYYY and ZZZZZZ for both the segments.

For any chosen month and segment in the second sheet, i need to display (code-wise) the chosen month's no.s and the average of the last 3 months from the chosen month for all the 3 types XXXXXX, YYYYYY and ZZZZZZ. The average is computed in the last column for each type in the 1st sheet itself.

For example, if the user chooses segment C and the current month (Jan 09), then for any code value, the average of last 3 months (Oct 08 to Dec 08) needs to be calculated and displayed, followed by the current month's value for that code in the adjacent column.

Since the average of the last 3 months will differ when the user changes the segment/month in the 2nd sheet, this average needs to be computed for each code and type (XXXXXX, YYYYYY and ZZZZZZ) everytime.

How can this average be computed using the offset function dynamically when the month/segment is changed by the user?

P.S.: When no month/segment is chosen, all cells should display 0 by default.

Ranga

Bob Phillips
01-21-2009, 09:56 AM
'DS Data'!W3:

=IF('DS Efficiency'!$D$26="",0,SUM(INDEX($E3:$V3,MAX(1,MATCH('DS Efficiency'!$D$26,$E$2:$V$2,0)-3)):INDEX($E3:$V3,MAX(1,MATCH('DS Efficiency'!$D$26,$E$2:$V$2,0)-1))))

and copy down